Skip to Main Content

 
Titelbild Muniqsoft Training

Auswahl  

Komplett Übersicht aller Oracle Tipps

Tablespace Shrink available as of Oracle 23ai 

Oracle
DBA
RDBMS 23.1
27.11.24 (MP)
05.04.25(MP)

Passende Schulungen zum Thema

I've been waiting 30 years for this feature, but as they say – better late than never…

Starting with Oracle 23ai, it is finally possible to reorganize a tablespace. There are just a few limitations:

  1. It must be a Bigfile Tablespace

  2. It must not contain any exotic or read-only objects such as
    AUD$UNIFIED

First, let's check which tablespaces are actually worth reorganizing.

set serveroutput on linesize 400
set long 9999999
WITH function analyze_tbs(tbs_name In VARCHAR2) RETURN CLOB
IS
PRAGMA AUTONOMOUS_TRANSACTION;
v_result CLOB:=empty_clob();
BEGIN
dbms_output.put_line('###  '||tbs_name||'  ###:');
DBMS_SPACE.TABLESPACE_SHRINK(tbs_name, 
SHRINK_MODE => DBMS_SPACE.TS_MODE_ANALYZE, 
SHRINK_RESULT => v_result);
RETURN tbs_name||':'||chr(10)||v_result;
END;
select analyze_tbs(tablespace_name)
from dba_tablespaces
where bigfile='YES'
and contents='PERMANENT';

 

Now, we are finally able to shrink or reorganize a tablespace in Oracle...

BEGIN
DBMS_SPACE.TABLESPACE_SHRINK('SYSAUX', 
SHRINK_MODE => dbms_space.ts_mode_shrink, 
target_size => dbms_space.ts_target_max_shrink);
END;
/

Have fun with the newly reclaimed space. The savings can now be invested in a training course. :-)