Cautions while dropping a tablespace

DROP TABLESPACE drops the tablespace from database. But, there are few things which you should take care while firing this statement.

1. DROP TABLESPACE myTablespace;

- drops the tablespace

Cautions:

A) You cannot drop the SYSTEM tablespace.

B) You can drop the SYSAUX tablespace only if you have the SYSDBA system privilege and you have started the database in MIGRATE mode.

C) You cannot use this statement to drop a tablespace group. However, if tablespace is the only tablespace in a tablespace group, then Oracle Database removes the tablespace group from the data dictionary as well.

D) When you drop a tablespace, Oracle Database does not place it in the recycle bin means your data is not recoverable. Therefore, make sure that all data contained in a tablespace to be dropped will not be required in the future.

E) Also, immediately before and after dropping a tablespace from a database, back up the database completely. This is strongly recommended so that you can recover the database if you mistakenly drop a tablespace, or if the database experiences a problem in the future after the tablespace has been dropped.

2. DROP TABLESPACE myTablespace

    INCLUDING CONTENTS

    CASCADE CONSTRAINTS;

-drops the myTablespace tablespace and drops all referential integrity constraints that refer to primary and unique keys inside myTablespace

Cautions:

1. For partitioned tables, DROP TABLESPACE will fail even if you specify INCLUDING CONTENTS, if the tablespace contains some, but not all. If all the partitions of a partitioned table reside in tablespace, then DROP TABLESPACE ... INCLUDING CONTENTS will drop tablespace.

3. DROP TABLESPACE myTablespace

   INCLUDING CONTENTS AND DATAFILES;

-drops the myTablespace tablespace and deletes all associated operating system datafiles.

Suggestion: You can drop a tablespace regardless of whether it is online or offline. But it is suggested that you take the tablespace offline before dropping it to ensure that no SQL statements in currently running transactions access any of the objects in the tablespace.