Systems Engineering and RDBMS

Tablespace Maintenance Options

Posted by decipherinfosys on October 22, 2007

In one of our previous blog post, we had discussed how to go about making tablespaces read only. In this blog post, we will discuss about some more tablespace maintenance options namely renaming tablespace and making it offline and change it back online and removing the tablespace. Please look at our above mentioned article to create tablespace. We are going to use same tablespace in this article to perform maintenance operations.

Renaming tablespace: We can rename permanent or temporary tablespace by using ALTER TABLESPACE command.

SQL> ALTER TABLESPACE TEST_TB RENAME TO TEST_TBLSPACE
2 /

Tablespace altered.

One can rename tablespace only when tablespace itself and all its data files are online and compatible parameter in init.ora file is set to 10.0.0 or higher. SYSTEM and SYSAUX tablespace cannot be renamed. Oracle takes care of changing the references to new name in all metadata related tables and views.

Taking tablespace offline/Online: Some times there is a need to take tablespace offline for maintenance purpose. Let us assume that there is not enough space on the drive for a data file to grow and we need to move the data files to different location. In this case, first we need to take tablespace offline, move the data files to new location and then bring back the tablespace online. (We are not showing here actual steps involved in the operation. We will cover this in future blog.) We will just show the syntax here for taking tablespace offline and back online.

SQL> ALTER TABLESPACE TEST_TBLSPACE OFFLINE;

Tablespace altered.

Similarly to bring the tablespace back online, alter tablespace again.

SQL> ALTER TABLESPACE TEST_TBLSPACE ONLINE;

Tablespace altered.

One cannot take system, undo or temporary tablespace offline. If attempt is made to take any of these tablespaces offline, Oracle will throw an error and will not allow the operation. Another thing to remember is that, when tablespace is taken offline, all of its data files are also taken offline and further access to its segments is prevented.

Dropping tablespaces: Tablespaces can be dropped using DROP TABLESPACE command. If there are objects in the tablespace, i.e. if tablespace is not empty, we need to include ‘INCLUDING CONTENTS’ clause. This clause recursively drops all the objects in the tablespace before dropping the tablespace. Dropping tablespace does not drop data files associated with the tablespaces. In order to drop tablespace and its associated data files we need to use ‘ INCLUDING CONTENTS AND DATAFILES’ clause. Following is the command to drop tablespace and all its contents.

SQL> DROP TABLESPACE TEST_TBLSPACE INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

‘ALTER TABLESPACE’ privilege allows to perform any alter tablespace operation. But ‘MANAGE TABLESPACE’ privilege allows only following operation.

• Taking tablespace offline/online.
• Making tablespace read only/read write.
• Begin or end a backup.

Sorry, the comment form is closed at this time.

 
%d bloggers like this: