Dropping a tablespace

To begin with, it is better to drop a tablespace if it is unusable and consume  a lot of resources. And before dropping a tablespace it is recommended to make it offline.

Here below is  some of the SQL statements to drop a tablespace based on the constraints and datafiles that the tablespace has:

—-Make the tablespace offline

SQL> ALTER TABLESPACE emp_tsdata  OFFLINE;

—-For tablespaces that does not contain data files

SQL> DROP TABLESPACE emp_tsdata;

—- For tablespaces that have data files

SQL> DROP TABLESPACE emp_tsdata INCLUDING CONTENTS AND DATAFILES;

—-For tablespace has referenced constraints

SQL> DROP TABLESPACE emp_tsdata INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;