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
1 | SQL> ALTER TABLESPACE emp_tsdata OFFLINE; |
—-For tablespaces that does not contain data files
1 | SQL> DROP TABLESPACE emp_tsdata; |
—- For tablespaces that have data files
1 | SQL> DROP TABLESPACE emp_tsdata INCLUDING CONTENTS AND DATAFILES; |
—-For tablespace has referenced constraints
1 | SQL> DROP TABLESPACE emp_tsdata INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS; |
meda Edited answer 22/02/2022