Oracle 23ai: Bigfile Tablespace Shrink

 

- Overview:

• If you try to resize/shrink a datafile before Oracle Database 23ai, you'll get "ORA-03297: file contains used data beyond requested RESIZE value", because there are segments' extents (your tables, indexes, partitions, etc.) that are blocking it from shrinking..
• Oracle 23ai database introduces the option to shrink a Bigfile tablespace to reclaim datafile's free space.
• A new procedure (SHRINK_TABLESPACE) has been added to DBMS_SPACE package to shrink tablespace and reclaim datafile's free space.

In this blog, I'll demonstrate the steps to shrink a tablespace.

Prerequisites:

• Oracle Database 23ai.

Preparation Steps:

 1. Create a new Bigfile tablespace.

   - Connect to a pluggable database and create a new tablespace.

2. Create new tables on the new tablespace and populate with data.

  - In my example, I used below commands to create and populate tables with data.

  - Create tables:

create table segments tablespace TEST_SHRINK as select * from dba_segments;

create table objects tablespace TEST_SHRINK as select * from dba_objects;

create table my_tables tablespace TEST_SHRINK as select * from dba_tables;

  - Populate tables by running below inserts multiple times:

insert into segments select * from segments; 

insert into objects select * from objects;

insert into my_tables select * from my_tables;

  - Check datafile's free space and write it down for comparison later.

3. Delete some data or truncate table/s. 

  - In my example, I truncated OBJECTS table.

  - Notice that datafile's free space increased from 316M to 724M.

Shrink Tablespace Steps:

1. Analyze a tablespace shrink by executing below command.

execute dbms_space.shrink_tablespace('TEST_SHRINK',shrink_mode => DBMS_SPACE.TS_MODE_ANALYZE)

  - It is highly recommended to determine the potential of a shrink operation by running it in analyze mode first. 

  - The result of this analysis contains useful information including a list of unsupported objects, a list of movable objects, the total size of movable objects in the tablespace, and the suggested target size for the tablespace. 

  - Analyzing a tablespace will take much less time that actually shrinking it.

2. Execute shrink tablespace by executing below command.

execute dbms_space.shrink_tablespace('TEST_SHRINK')

  - Notice that datafile's size was shrunk from 1024M to 202M and datafile's free space was decreased from 724M to 7M.