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:
It must be a Bigfile Tablespace
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. :-)