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
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
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
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
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