Oracle 23ai Tablespace shrink

Table space shrink on 23ai !!!!



Using the shrink_tablespace procedure is an easy way to reorganize objects, so we can reclaim free space in the tablespace.

Oracle Database 23ai introduces the shrink_tablespace procedure, which simplifies the process of resizing datafiles and organizing objects.

shrink_tablespace can be used in three modes: 
Default - You can specify the tablespace and let the process shrink the file as small as possible (this uses an online move).
Analyze - Get information about the shrink prior to doing it.
Force - For the instances where you don't care if the move is done online or offline, the procedure tries to move objects online (the default), and if it fails, an offline move is used.


sqlplus / as sysdba

SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Wed Nov 13 09:37:45 2024
Version 23.5.0.24.07

Copyright (c) 1982, 2024, Oracle.  All rights reserved.


Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.5.0.24.07



set serveroutput on



SQL> BEGIN dbms_space.shrink_tablespace('SYSTEM', shrink_mode => DBMS_SPACE.TS_MODE_ANALYZE); END;
  2  /

PL/SQL procedure successfully completed.

SQL> set serveroutput on
SQL> /
-------------------ANALYZE RESULT-------------------
Total Movable Objects: 2
Total Movable Size(GB): .06
Original Datafile Size(GB): 6.88
Suggested Target Size(GB): 1.11
Process Time: +00 00:00:01.177637

PL/SQL procedure successfully completed.

SQL> execute dbms_space.shrink_tablespace('SYSTEM');


-------------------SHRINK RESULT-------------------
Total Moved Objects: 2
Total Moved Size(GB): .06
Original Datafile Size(GB): 6.88
New Datafile Size(GB): 6.21
Process Time: +00 00:00:26.764780

PL/SQL procedure successfully completed.