Dropping Tablespaces

To remove a tablespace from the data dictionary and therefore from the database, you need to drop that tablespace. In Oracle 9i, you can also drop the data file associated with the tablespace by specifying the AND DATAFILES clause:

 DROP TABLESPACE mydb1data INCLUDING CONTENTS AND DATAFILES; 

The following list provides all the possible parameters to the DROP TABLESPACE command along with the meanings of those parameters:

 DROP TABLESPACE tablespacename INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS; 

• INCLUDING CONTENTS Causes all the segments in the tablespace to be dropped.

• AND DATAFILES (or INCLUDING DATAFILES) Causes all the data files associated with the tablespace to be dropped from the operating system.

• CASCADE CONSTRAINTS Causes all the referential integrity constraints from other tablespaces' tables that refer to primary and unique keys on the tables in the dropped tablespace to be themselves dropped.

• tablespacename Name of the tablespace to be dropped. This parameter is required.

A tablespace that still contains data cannot be dropped without specifying the INCLUDING CONTENTS option. This can create significant undo information if the tablespace contains many objects. After the tablespace has been dropped, the data associated with that tablespace is no longer in the database. If you don't specify the AND DATAFILES parameter, only the pointers in the control files that reference the data files associated with the tablespace are dropped along with the associated data dictionary information. This means that the data files still exist and must be deleted explicitly with the appropriate operating system commands.

It is recommended that you take offline any tablespace that you intend to drop, even read-only tablespaces, so that no transactions are accessing the segments in the tablespace when the drop command is issued.