DBA Fundamentals

Maintaining a Tablespace

 
Introduction
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 a 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
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.
SQL> CONNECT system/manager@yourhost AS SYSDBA
 

View tablespace information
Query the DBA_TABLESPACES directory view to display the tablespace name and status columns.
SQL> SELECT tablespace_name, status
FROM dba_tablespaces
/
Take notes on the STATUS of the USERS tablespace.
 

Populate a table 
Now, create a table in the USERS tablespace and write a stored procedure to populate that table.
SQL> CREATE TABLE table1
(col1 number, col2 varchar2(20))
TABLESPACE users
/
(Procedure to populate it�)
SQL> BEGIN
SQL> FOR i IN 1..100 LOOP
SQL> INSERT INTO table1 VALUES (i, 'AA' || i);
SQL> END LOOP;
SQL> COMMIT;
SQL> END;
SQL> /
The table should be populated with 100 inserted records.
 

Change tablespace status
Now, alter the USERS tablespace status to READ ONLY.
SQL> ALTER TABLESPACE users READ ONLY
/

Query the DBA_TABLESPACES view to display the tablespace name and status columns again.
SQL> 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.
 

Create a table in the READ ONLY tablespace.
SQL> CREATE TABLE table2
(col1 number, col2 varchar2(20))
TABLESPACE users
/
Notice that you cannot create any tables in that tablespace.

Try to add a record into table1 which is in the READ ONLY tablespace.
SQL> INSERT INTO table1 VALUES (100, 'AA100')
/
Notice that a record cannot be added at this time, nor you can drop the table.

Change a tablespace status to READ WRITE mode
Change the tablespace status to the READ WRITE mode.
SQL> ALTER TABLESPACE users READ WRITE
/

Drop the table.
SQL> DROP TABLE table1
/
Now, you can drop the table, since the tablespace status is READ WRITE.

Relocate a tablespace
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.
SQL> SELECT file_name, status 
FROM dba_data_files
WHERE tablespace_name = 'USERS'
/
Take a note of its status.

Set the USERS tablespace status to OFFLINE.
SQL> ALTER TABLESPACE users OFFLINE
/

Now, copy the USERS datafile to the newfolder subdirectory and then, delete the original.
SQL> -- Copy the file(s).
SQL> HOST COPY C:\oracle\oradata\school\users01.dbf c:\newfolder\*.*
SQL> -- Erase the file(s).
SQL> HOST ERASE C:\oracle\oradata\school\users01.dbf

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.
SQL> ALTER DATABASE RENAME FILE
'c:\oracle\oradata\school\users01.dbf'
TO
'c:\newfolder\users01.dbf'
/

Change the tablespace status to ONLINE.
SQL> ALTER TABLESPACE users ONLINE
/

Query the DBA_DATA_FILES view to check the relocation.
SQL> SELECT file_name, status 
FROM dba_data_files
WHERE tablespace_name = 'USERS'
/

Looks like the relocation process was completed successfully. Now, relocate the datafile back to its original location.

Relocate a tablespace 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.

Offline the tablespace.
SQL> ALTER TABLESPACE users OFFLINE
/

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

Erase the file(s).
SQL> HOST ERASE C:\newfolder\users01.dbf

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

Online the tablespace.
SQL> ALTER TABLESPACE users ONLINE
/

Query the DBA_DATA_FILES view to check the relocation.
SQL> SELECT file_name, status 
FROM dba_data_files
WHERE tablespace_name = 'USERS'
/

It looks like the relocation process was completed successfully.

Questions:

Q: How do you change a tablespace mode to the READ ONLY mode?
Q: How do you relocate a tablespace?
Q: How do you change a tablelspace mode to the READ WRITE mode?
Q: How do you OFFLINE a tablespace mode?
Q: How do you ONLINE a tablespace mode?
Q: When do you OFFLINE a tablespace mode?
Q: When can you perform the following SQL statement? 
SQL> ALTER DATABASE RENAME FILE
'c:\oracle\oradata\school\users01.dbf'
TO
'c:\newfolder\users01.dbf'
/