Reduce the size of big and small tablespace in Oracle 23ai

In Oracle 23ai, a new DBMS_SPACE procedure is introduced to automatically reorganize objects and free up space in data files for the Operating System.

select  file_name, blocks, bytes/1024/1024 as size_mb
from   dba_data_files
where  tablespace_name = 'USERS';

Analyze the tablespace with ANALYZE option:

Note: First Parameter is tablespace name for package dbms_space.shrink_tablespace

Note: The shrink_mode second parameter for package dbms_space.shrink_tablespace

• The default mode is TS_SHRINK_MODE_ONLINE, which means it usually moves objects online, except for index-organized tables.
• TS_SHRINK_MODE_AUTO also moves objects online first, but if that fails, it will try to move them offline.
• TS_SHRINK_MODE_OFFLINE provides the best results and performance when shrinking but object is not able to use during operation.

set serveroutput on
execute dbms_space.shrink_tablespace('USERS', shrink_mode => DBMS_SPACE.TS_SHRINK_MODE_ANALYZE);

------------------------------ANALYZE RESULT--------------------------------------
1. { HR.SYS_IL0000034555C00002$$ | type: INDEX | blocks: 256 | tablespace_name: USERS }
2. { HR.T2 | type: TABLE | blocks: 512 | tablespace_name: USERS  }
3. { HR.T2_LOB1 | type: LOBSEGMENT | blocks: 45824 | tablespace_name: USERS }
4. { HR.T5 | type: TABLE | blocks: 512 | tablespace_name: USERS }
Total Movable Objects: 4
Total Movable Size(GB): 1.12
Orginal Datafile Size(GB): 7
Suggested Target Size(GB): 3.45
Process Time: +00 00:00:02.45783

Shrink tablespace USERS in Oracle 23ai with DBMS_SPACE procedure

set serveroutput on
execute dbms_space.shrink_tablespace('USERS');

-------------------SHRINK RESULT-------------------
Total Moved Objects: 4
Total Moved Size(GB): 1.12
Orginal Datafile Size(GB): 7
New Datafile Size(GB): 4
Process Time: +00 00:00:30.24674

Note: The TS_MODE_SHRINK_FORCE will move objects offline if they can’t be moved online. Avoid using this option if it could cause issues in your application.