In previous post, we have talked about how to identify empty tablespaces for dropping. In that post, we cautiously check the status of the tablespace and some of them are confirmed as empty.
In a similar way, we should drop empty tablespaces carefully.
Take Empty Tablespaces Offline
We should take empty tablespaces offline to make sure that they are not being used by anyone. In other words, if you received complains after taking them offline, you may not drop them at this moment.
Please note that, taking a tablespace offline is not a prerequisite for dropping it. That is to say, you can drop it when it's online as long as it's not being used.
Prepare Offline Statements
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;
STMT
--------------------------------------------------------------------------------
ALTER TABLESPACE ERPTBS_16 OFFLINE;
ALTER TABLESPACE ERPTBS_20 OFFLINE;
ALTER TABLESPACE ERPTBS_41 OFFLINE;
ALTER TABLESPACE ERPTBS_42 OFFLINE;
ALTER TABLESPACE ERPTBS_86 OFFLINE;
...
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
Then we check their status for sure.
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'));
TABLESPACE_NAME STATUS
------------------------------ ---------
ERPTBS_16 OFFLINE
ERPTBS_20 OFFLINE
ERPTBS_41 OFFLINE
ERPTBS_42 OFFLINE
ERPTBS_86 OFFLINE
...
Then we wait, the longer the better. If anyone came to you about this, you know something needs to be dealt with.
Drop Empty Tablespaces
Since we planned to drop a bunch of tablespaces, so we need to compose dropping statements in advance. However, to drop single normal tablespace, it's simpler.
Prepare Dropping Statements
We prepare 2 sets of dropping statement, one is simple drop, the other is drop it including datafiles.
Drop Tablespace without Modifiers
Such 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;
STMT
--------------------------------------------------------------------------------
DROP TABLESPACE ERPTBS_16;
DROP TABLESPACE ERPTBS_20;
DROP TABLESPACE ERPTBS_41;
DROP TABLESPACE ERPTBS_42;
DROP TABLESPACE ERPTBS_86;
...
The simple statements prevent you from dropping non-empty tablespaces, which may be safer to do it in a production environment. However, you need to remove datafiles manually.
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;
TABLESPACE_NAME FILE_NAME
-------------------- --------------------------------------------------
ERPTBS_16 /oradata/ORCLCDB/ORCLPDB/erptbs_16_01.dbf
ERPTBS_20 /oradata/ORCLCDB/ORCLPDB/erptbs_20_01.dbf
ERPTBS_41 /oradata/ORCLCDB/ORCLPDB/erptbs_41_01.dbf
ERPTBS_42 /oradata/ORCLCDB/ORCLPDB/erptbs_42_01.dbf
ERPTBS_86 /oradata/ORCLCDB/ORCLPDB/erptbs_86_01.dbf
ERPTBS_86 /oradata/ORCLCDB/ORCLPDB/erptbs_86_02.dbf
...
A potential problem is that, is it possible to remove the wrong datafile? It's possible, human intervention could make mistakes. So I would recommend moving (i.e. mv) them to another place instead of deleting (i.e. rm) them.
Of course, if you're pretty sure that they are empty, you can drop the tablespaces as well as their datafiles. Let's see the statements.
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;
STMT
--------------------------------------------------------------------------------
DROP TABLESPACE ERPTBS_16 INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE ERPTBS_20 INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE ERPTBS_41 INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE ERPTBS_42 INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE ERPTBS_86 INCLUDING CONTENTS AND DATAFILES;
...
Be careful, there's no warning except for referential relationship with other tablespace.
Issue Dropping Statements
Since we want to drop tablespaces and their datafiles on both the primary and standby databases, we choose the second sets of dropping statements.
SQL> drop tablespace erptbs_16 including contents and datafiles;
Tablespace dropped.
SQL> drop tablespace erptbs_20 including contents and datafiles;
Tablespace dropped.
SQL> drop tablespace erptbs_41 including contents and datafiles;
Tablespace dropped.
SQL> drop tablespace erptbs_42 including contents and datafiles;
Tablespace dropped.
SQL> drop tablespace erptbs_86 including contents and datafiles;
Tablespace dropped.
As you can see, we deliberately and cautiously arrange steps to drop empty tablespaces in the above, this is because we don't want to get any trouble, especially for production environments.