- 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.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.
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.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
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.
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.
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.
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
SQL> drop tablespace users_1 including contents and datafiles; Tablespace dropped. SQL> drop tablespace users_2 including contents and datafiles; Tablespace dropped.