Hands-On 12 (Maintaining a Tablespace)

As a DBA, you are responsible for maintaining tablespaces and datafiles due to a user’s usage of the tablespace. If your user does not update any tables in the tablespace, you may want to change the tablespace mode to the READ ONLY mode. Or if you have any I/O problems on a disk, you can relocate the tablespace to a new not busy disk. Your job’s responsibilities dictate that you should at least be informed of the following basic fundamental subjects:

Maintaining a tablespace mode

READ ONLY

Relocating the tablespace

Using OMF

Without using OMF

Using the DBA_TABLESPACES dictionary view

TABLESPACE_NAME

STATUS

Altering a tablespace mode to READ ONLY

Altering a tablespace mode to READ WRITE

Performing activities in a READ ONLY tablespace mode

Dropping table in a READ ONLY mode

Using the DBA_TABLESPACES view

Relocating a tablespace

Copying a datafile

Altering the database to a new location

Changing a tablespace status

Commands:

ALTER TABLESPACE READ ONLY

ALTER TABLESPACE READ WRITE

ALTER TABLESPACE OFFLINE

ALTER TABLESPACE ONLINE

HOST COPY

HOST ERASE

ALTER DATABASE RENAME FILE

-- Hands-On 12 (Maintaining a Tablespace)
-- Preparation
set echo on
connect system/manager@school as sysdba
SET linesize 1000 pagesize 55
SET SQLPROMPT 'SQL> '
COL name FORMAT a50
col file_name format a50
col extent_management format a20
col username format a10
col member format a50

pause

--Start

CLEAR SCR
-- In this exercise you will learn how to maintain the
-- tablespace mode (READ ONLY) and relocate the tablespace
-- with or without using Oracle-Managed Files (OMF) and more.

-- Now, let's connect to SQL*Plus as the system/manager user.

pause

CONNECT system/manager@school AS SYSDBA

pause

CLEAR SCR
-- Query the DBA_TABLESPACES directory view to display the
-- tablespace name and status columns.

pause

SELECT tablespace_name, status
FROM dba_tablespaces
/

-- Take notes on the STATUS of the USERS tablespace.

pause

CLEAR SCR
-- Now, create a table in the USERS tablespace and write
-- a stored procedure to populate that table.

pause


CREATE TABLE table1
(col1 number, col2 varchar2(20))
TABLESPACE users
/


BEGIN
FOR i IN 1..100 LOOP
INSERT INTO table1 VALUES (i, 'AA' || i);
END LOOP;
COMMIT;
END;
/

-- The table was populated with 100 inserted records.

pause

CLEAR SCR
-- Now, alter the USERS tablespace status to READ ONLY.

pause


ALTER TABLESPACE users READ ONLY
/

pause

CLEAR SCR
-- Query the DBA_TABLESPACES view to display the tablespace
-- name and status columns again.

pause

SELECT tablespace_name, status
FROM dba_tablespaces
/

-- Take notes on the STATUS of the USERS tablespace.
-- Note that it is in the READ ONLY mode.

pause


CLEAR SCR
-- Create a table in the READ ONLY tablespace.

pause

CREATE TABLE table2
(col1 number, col2 varchar2(20))
TABLESPACE users
/

-- Notice that you cannot create any tables in that 
-- tablespace.

pause


CLEAR SCR
-- Try to add a record into table1 which is in the 
-- READ ONLY tablespace.

pause

INSERT INTO table1 VALUES (100, 'AA100')
/

-- Notice that a record cannot be added at this time.

pause


CLEAR SCR
-- Change the tablespace status to the READ WRITE mode.

pause


ALTER TABLESPACE users READ WRITE
/

pause

CLEAR SCR
-- Drop the table.

pause


DROP TABLE table1
/

pause

CLEAR SCR
-- Now, let's relocate the USERS tablespace from
-- c:\oracle\oradata\school\users01.dbf to
-- c:\newfolder\users01.dbf.

-- Query the DBA_DATA_FILES view to check where the USERS
-- tablespace is located.

pause


SELECT file_name, status 
FROM dba_data_files
WHERE tablespace_name = 'USERS'
/

-- Take a note of its status.

pause

CLEAR SCR
-- Set the USERS tablespace status to OFFLINE.

pause


ALTER TABLESPACE users OFFLINE
/

pause

CLEAR SCR
-- Now, copy the USERS datafile to the newfolder subdirectory
-- and then, delete the original.

pause


--\/-- Copy the file(s).
HOST COPY C:\oracle\oradata\school\users01.dbf c:\newfolder\*.*

--\/-- Erase the file(s).
HOST ERASE C:\oracle\oradata\school\users01.dbf

pause

CLEAR SCR
-- Alter the database and change the original location to the
-- new location. Notice that the alter statement will change
-- and update the content of the controlfile since the database
-- structure was changed.

pause

ALTER DATABASE RENAME FILE
'c:\oracle\oradata\school\users01.dbf'
TO
'c:\newfolder\users01.dbf'
/

pause

CLEAR SCR
-- Change the tablespace status to ONLINE.

pause


ALTER TABLESPACE users ONLINE
/

pause

CLEAR SCR
-- Query the DBA_DATA_FILES view to check the relocation.

pause


SELECT file_name, status 
FROM dba_data_files
WHERE tablespace_name = 'USERS'
/

-- Looks like the relocation process was completed successfully.

pause

CLEAR SCR
pause
pause

CLEAR SCR
-- Now, relocate the datafile back to its original location.

-- 1) Set the USERS tablespace status back to OFFLINE.
-- 2) Copy the USERS datafile from the newfolder back to its original location.
-- 3) The datafile.
-- 4) Alter the database to rename the datafile location.
-- 5) Then, set the tablespace status to ONLINE.

pause

--\/-- Offline the tablespace.
ALTER TABLESPACE users OFFLINE
/

--\/-- Copy the file(s).
HOST COPY c:\newfolder\users01.dbf C:\oracle\oradata\school\users01.dbf 

--\/-- Erase the file(s).
HOST ERASE C:\newfolder\users01.dbf

--\/-- Alter the database.
ALTER DATABASE RENAME FILE
'c:\newfolder\users01.dbf' TO 'c:\oracle\oradata\school\users01.dbf'
/

--\/-- Online the tablespace.
ALTER TABLESPACE users ONLINE
/

pause

CLEAR SCR
-- Query the DBA_DATA_FILES view to check the relocation.

pause


SELECT file_name, status 
FROM dba_data_files
WHERE tablespace_name = 'USERS'
/

-- It looks like the relocation process was completed successfully.

pause

CLEAR SCR
-- Now, you should practice this Hands-On exercise.

-- For more information about the subject, you are encouraged
-- to read from a wide selection of available books.

-- Good luck.
--
pause
pause