Dropping Tablespaces
Posted by thakuranupam on November 8, 2009
The following list provides all the possible parameters to the DROP TABLESPACE command:
DROP TABLESPACE tablespacename
INCLUDING CONTENTS
[AND DATAFILES | INCLUDING 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
A tablespace that still contains data cannot be dropped without specifying the INCLUDING CONTENTS option.
If AND DATAFILES parameter is not specified, 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 to take offline any tablespace that is intended to drop, even read-only tablespaces, so that no transactions are accessing the segments in the tablespace when the drop command is issued.
