Oracle 23ai: Bigfile Tablespace Shrink

Share on:

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

Share on:

More from this Author

Oracle 23ai JSON Schema Support

Oracle 23ai: JSON Schema Support

Overview: Oracle first introduced the ability to display a JSON schema when they introduced the JSON Data in Oracle 12.2 but without the option to ... Read More

Oracle 23ai Enumeration Domains – List of Values in the Database

Oracle 23ai: Enumeration Domains – List of Values in the Database

Overview: Oracle Database 23ai introduces a new way to create lists of values: enumeration (enum) domains Benefits: Create lists of name-value pairs ... Read More

Back to Top