Skip to content

How to Shrink Empty Tablespace

  • Oracle

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.

Leave a Reply

Your email address will not be published. Required fields are marked *