We take the following steps to drop a tablespace.
1. Make Sure Non-Default
The tablespace that we want to drop must not be the default permanent tablespace of the database or the default temporary tablespace of the database.
SQL> select count(*) cnt from database_properties where property_value = 'ERPTBS';
CNT
----------
0Optionally, make sure the tablespace is not the default tablespace of users for preventing unexpected errors.
SQL> select count(*) cnt from dba_users where default_tablespace = 'ERPTBS' or temporary_tablespace = 'ERPTBS' or local_temp_tablespace = 'ERPTBS';
CNT
----------
02. Take Tablespace Offline
Before dropping a tablespace, we should make sure the tablespace is OFFLINE.
SQL> select status from dba_tablespaces where tablespace_name = 'ERPTBS';
STATUS
---------
ONLINEIf it is online, we should take it offline.
SQL> alter tablespace erptbs offline;
Tablespace altered.
SQL> select status from dba_tablespaces where tablespace_name = 'ERPTBS';
STATUS
---------
OFFLINEIt may take some times to bring it offline during busy hours.
3. Drop Tablespace
There're 4 scales to drop a tablespace.
Simple
If the tablespace contains no object, we can do a simple drop without specifying anything.
SQL> drop tablespace erptbs;
Tablespace dropped.The operation leaves its data files without removing them.
Content
If the tablespace contains one or many object, we can remove the content from the data files of the tablespace by adding a modifier INCLUDING CONTENTS to the statement.
SQL> drop tablespace erptbs including contents;
Tablespace dropped.The operation clears any segments in it and leaves data files in the file system. If you want to reuse the data file to create a tablespace, you need to use REUSE keyword.
Data Files
If you would like to remove the tablespace physically, you may drop the data files at the same time.
SQL> drop tablespace erptbs including contents and datafiles;
Tablespace dropped.The tablespace is removed as well as data files.
Constraints
If there's any outside constraints, mostly referential integrity ones depend on objects in this tablespace, you can remove them as well.
SQL> drop tablespace erptbs including contents and datafiles cascade constraints;
Tablespace dropped.