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 the datafile’s free space
  • A new procedure (SHRINK_TABLESPACE) has been added to the DBMS_SPACE package to shrink tablespace and reclaim the data file’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

oracle

oracle2

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

  – In my example, I used the 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 the datafile’s free space and write it down for comparison later

oracle3

3. Delete some data or truncate table/s. 

  – In my example, I truncated the OBJECTS table.

  – Notice that the datafile’s free space increased from 316M to 724 M

oracle4

 

Shrink Tablespace Steps:

1. Analyze a tablespace shrink by executing the 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 than actually shrinking it

oracle5

2. Execute shrink tablespace by executing the below command

execute dbms_space.shrink_tablespace('TEST_SHRINK')

  – Notice that the datafile’s size was shrunk from 1024M to 202M and the datafile’s free space was decreased from 724M to 7M

oracle6

oracle7