We take the following steps to drop a tablespace.
- Make Sure Tablespace is Non-Default
- Take Tablespace Offline
- Make Sure Tablespace is Empty
- Drop a Tablespace
Make Sure Tablespace is 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.
Next, we query DATABASE_PROPERTIES to make sure it.
SQL> select count(*) cnt from database_properties where property_value = 'ERPTBS';
CNT
----------
0
Optionally, 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
----------
0
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
---------
ONLINE
If 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
---------
OFFLINE
It may take some times to bring it offline during busy hours.
Make Sure Tablespace is Empty
We should make sure that the tablespace we want to drop is empty, otherwise we could have some trouble (ORA-01549) later. This step can be ignored if you're intent to remove all contents in it.
SQL> select count(*) cnt from dba_segments where tablespace_name = 'ERPTBS';
CNT
----------
0
0 means that no segment in this tablespace.
Drop a Tablespace
There're 4 scales to drop a tablespace.
drop tablespace
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.
drop tablespace including contents
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.
drop tablespace including contents and datafiles
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.
drop tablespace including contents and datafiles cascade 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.