- 21 Nov, 2023
- 0 Comments
- 2 Mins Read
How to Drop Empty Tablespace?
How to Drop Empty Tablespace ?
Prepare Offline Statements :
We should take empty tablespaces offline to make sure that they are not being used by anyone. First of all, we prepare statements for taking those tablespaces offline.1 | SQL> select 'ALTER TABLESPACE ' || name || ' OFFLINE;' stmt from v$tablespace where ts# > 6 and name not in (select distinct tablespace_name from dba_segments) and name not in (select tablespace_name from dba_tablespaces where contents = 'TEMPORARY') order by 1; |
Exclude Reserved Tablespaces
If there’s any tablespaces which should be excluded from the list, you may add a filter and re-arrange the list like this.1 | SQL> select 'ALTER TABLESPACE ' || name || ' OFFLINE;' stmt from v$tablespace where ts# > 6 and name not in (select distinct tablespace_name from dba_segments) and name not in (select tablespace_name from dba_tablespaces where contents = 'TEMPORARY') and name not in ('RESERVED_TBS1', 'RESERVED_TBS2') order by 1; |
Issue Offline Statements :
Next, we issue the statements.1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | SQL> ALTER TABLESPACE ERPTBS_16 OFFLINE;Tablespace altered.SQL> ALTER TABLESPACE ERPTBS_20 OFFLINE;Tablespace altered.SQL> ALTER TABLESPACE ERPTBS_41 OFFLINE;Tablespace altered.SQL> ALTER TABLESPACE ERPTBS_42 OFFLINE;Tablespace altered.SQL> ALTER TABLESPACE ERPTBS_86 OFFLINE;Tablespace altered. |
Check Status
1 | SQL> select tablespace_name, status from dba_tablespaces where tablespace_name in (select name tablespace_name from v$tablespace where ts# > 6 and name not in (select distinct tablespace_name from dba_segments) and name not in (select tablespace_name from dba_tablespaces where contents = 'TEMPORARY')); |
We prepare 2 sets of dropping statement, one is simple drop, the other is drop it including datafiles.
simple statement drops a tablespace if and only if the tablespace is empty.1 | SQL> select 'DROP TABLESPACE ' || name || ';' stmt from v$tablespace where ts# > 6 and name not in (select distinct tablespace_name from dba_segments) and name not in (select tablespace_name from dba_tablespaces where contents = 'TEMPORARY') and name not in ('RESERVED_TBS1', 'RESERVED_TBS2') order by 1; |
List Datafiles before Dropping
If you want to remove the files by yourself, you have to know the absolute path of their datafiles.
1 2 3 | SQL> column tablespace_name format a20;SQL> column file_name format a50;SQL> select tablespace_name, file_name from dba_data_files where tablespace_name in (select name tablespace_name from v$tablespace where ts# > 6 and name not in (select distinct tablespace_name from dba_segments) and name not in (select tablespace_name from dba_tablespaces where contents = 'TEMPORARY') and name not in ('RESERVED_TBS1', 'RESERVED_TBS2')) order by 1,2; |
Drop Tablespace Including Datafiles :
The statements drop tablespaces and their datafiles without conditions.
1 | SQL> select 'DROP TABLESPACE ' || name || ' INCLUDING CONTENTS AND DATAFILES;' stmt from v$tablespace where ts# > 6 and name not in (select distinct tablespace_name from dba_segments) and name not in (select tablespace_name from dba_tablespaces where contents = 'TEMPORARY') and name not in ('RESERVED_TBS1', 'RESERVED_TBS2') order by 1; |
Issue Dropping Statements
1 2 3 4 5 6 7 | SQL> drop tablespace users_1 including contents and datafiles;Tablespace dropped.SQL> drop tablespace users_2 including contents and datafiles;Tablespace dropped. |