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