Shrink Empty Tablespaces
Dropping empty tablespaces for reclaiming unused space is not a reversible operation, any mistake may cost you a lot in a production environment.
Alternatively, we can shrink them to a very small size, say 10 MB, to make them as smaller as possible. This can also reclaim their space significantly and safely.
First of all, let's see what datafiles in empty tablespaces larger than 10 MB need to be shrunk.
SQL> set pagesize 1000;
SQL> column tablespace_name format a20;
SQL> column file_name format a50;
SQL> column mb format 999,999;
SQL> select tablespace_name, file_name, bytes/1024/1024 MB 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')) and bytes/1024/1024 > 10 order by 1,2;
TABLESPACE_NAME FILE_NAME MB
-------------------- -------------------------------------------------- --------
CRMTBS_06 +DATA/ORCLCDB/ORCLPDB/crmtbs_06_01.dbf 9,900
ERPAPPTBS_03 +DATA/ORCLCDB/ORCLPDB/erpapptbs_03_01.dbf 370
ERPAPPTBS_10 +DATA/ORCLCDB/ORCLPDB/erpapptbs_10_01.dbf 490
ERPAPPTBS_13 +DATA/ORCLCDB/ORCLPDB/erpapptbs_13_01.dbf 110
ERPAPPTBS_14 +DATA/ORCLCDB/ORCLPDB/erpapptbs_14_01.dbf 80
ERPAPPTBS_16 +DATA/ORCLCDB/ORCLPDB/erpapptbs_16_01.dbf 280
ERPAPPTBS_21 +DATA/ORCLCDB/ORCLPDB/erpapptbs_21_01.dbf 220
ERPAPPTBS_22 +DATA/ORCLCDB/ORCLPDB/erpapptbs_22_01.dbf 20
ERPAPPTBS_30 +DATA/ORCLCDB/ORCLPDB/erpapptbs_30_01.dbf 160
ERPAPPTBS_40 +DATA/ORCLCDB/ORCLPDB/erpapptbs_40_01.dbf 550
ERPAPPTBS_50 +DATA/ORCLCDB/ORCLPDB/erpapptbs_50_01.dbf 460
ERPAPPTBS_60 +DATA/ORCLCDB/ORCLPDB/erpapptbs_60_01.dbf 280
12 rows selected.
ALTER DATABASE DATAFILE RESIZE
Let's see how we compose ALTER DATABASE DATAFILE RESIZE statements.
SQL> select 'ALTER DATABASE DATAFILE ''' || file_name || ''' RESIZE 10M;' stmt 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')) and bytes/1024/1024 > 10 order by 1;
STMT
--------------------------------------------------------------------------------
ALTER DATABASE DATAFILE '+DATA/ORCLCDB/ORCLPDB/crmtbs_06_01.dbf' RESIZE 10M;
ALTER DATABASE DATAFILE '+DATA/ORCLCDB/ORCLPDB/erpapptbs_03_01.dbf' RESIZE 10M;
ALTER DATABASE DATAFILE '+DATA/ORCLCDB/ORCLPDB/erpapptbs_10_01.dbf' RESIZE 10M;
ALTER DATABASE DATAFILE '+DATA/ORCLCDB/ORCLPDB/erpapptbs_13_01.dbf' RESIZE 10M;
ALTER DATABASE DATAFILE '+DATA/ORCLCDB/ORCLPDB/erpapptbs_14_01.dbf' RESIZE 10M;
ALTER DATABASE DATAFILE '+DATA/ORCLCDB/ORCLPDB/erpapptbs_16_01.dbf' RESIZE 10M;
ALTER DATABASE DATAFILE '+DATA/ORCLCDB/ORCLPDB/erpapptbs_21_01.dbf' RESIZE 10M;
ALTER DATABASE DATAFILE '+DATA/ORCLCDB/ORCLPDB/erpapptbs_22_01.dbf' RESIZE 10M;
ALTER DATABASE DATAFILE '+DATA/ORCLCDB/ORCLPDB/erpapptbs_30_01.dbf' RESIZE 10M;
ALTER DATABASE DATAFILE '+DATA/ORCLCDB/ORCLPDB/erpapptbs_40_01.dbf' RESIZE 10M;
ALTER DATABASE DATAFILE '+DATA/ORCLCDB/ORCLPDB/erpapptbs_50_01.dbf' RESIZE 10M;
ALTER DATABASE DATAFILE '+DATA/ORCLCDB/ORCLPDB/erpapptbs_60_01.dbf' RESIZE 10M;
12 rows selected.
Next, we perform shrinking statements.
SQL> ALTER DATABASE DATAFILE '+DATA/ORCLCDB/ORCLPDB/crmtbs_06_01.dbf' RESIZE 10M;
Database altered.
SQL> ALTER DATABASE DATAFILE '+DATA/ORCLCDB/ORCLPDB/erpapptbs_03_01.dbf' RESIZE 10M;
Database altered.
SQL> ALTER DATABASE DATAFILE '+DATA/ORCLCDB/ORCLPDB/erpapptbs_10_01.dbf' RESIZE 10M;
Database altered.
SQL> ALTER DATABASE DATAFILE '+DATA/ORCLCDB/ORCLPDB/erpapptbs_13_01.dbf' RESIZE 10M;
Database altered.
SQL> ALTER DATABASE DATAFILE '+DATA/ORCLCDB/ORCLPDB/erpapptbs_14_01.dbf' RESIZE 10M;
Database altered.
SQL> ALTER DATABASE DATAFILE '+DATA/ORCLCDB/ORCLPDB/erpapptbs_16_01.dbf' RESIZE 10M;
Database altered.
SQL> ALTER DATABASE DATAFILE '+DATA/ORCLCDB/ORCLPDB/erpapptbs_21_01.dbf' RESIZE 10M;
Database altered.
SQL> ALTER DATABASE DATAFILE '+DATA/ORCLCDB/ORCLPDB/erpapptbs_22_01.dbf' RESIZE 10M;
Database altered.
SQL> ALTER DATABASE DATAFILE '+DATA/ORCLCDB/ORCLPDB/erpapptbs_30_01.dbf' RESIZE 10M;
Database altered.
SQL> ALTER DATABASE DATAFILE '+DATA/ORCLCDB/ORCLPDB/erpapptbs_40_01.dbf' RESIZE 10M;
Database altered.
SQL> ALTER DATABASE DATAFILE '+DATA/ORCLCDB/ORCLPDB/erpapptbs_50_01.dbf' RESIZE 10M;
Database altered.
SQL> ALTER DATABASE DATAFILE '+DATA/ORCLCDB/ORCLPDB/erpapptbs_60_01.dbf' RESIZE 10M;
Database altered.
Let's check it again by the statement we introduced in the first place.
SQL> select tablespace_name, file_name, bytes/1024/1024 MB 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')) and bytes/1024/1024 > 10 order by 1,2;
no rows selected
We have shrunk them as much as possible.