Meet Your New Friend: DBMS_SPACE.SHRINK_TABLESPACE

How many times did you try to resize your tablespace or datafile and got the error “ORA-03297: file contains used data beyond requested RESIZE value”? Isn’t that a frustrating error, when you know there is hardly any data in the tablespace, and your file size is 100GB?

You might be imagining all the work that you must do: reorganize (aka move) all the tables in that tablespace to “compact” the data, generate the DDL to do all the work, potentially create an intermediary tablespace and so on. It really is a multi step process.

Now imagine how amazing it would be for a simple command to exist, that would do all this work for you, and shrink the tablespace. Well, that tool exists in 23ai: DBMS_SPACE.SHRINK_TABLESPACE.

The one caveat for this is, you can only use it on BIGFILE tablespaces, which thinking about it makes sense. When you resize a data file with the ALTER DATAFILE…RESIZE statement, or you resize a BIGFILE tablespace with the ALTER TABLESPACE…RESIZE statement, you can only resize it to just beyond the last used block in the data file. When you use DBMS_SPACE.SHRINK_TABLESPACE, it defragments the tablespace first, meaning it will compact the used blocks and place them together, ensuring there is no unused space between the blocks, then, it resizes the tablespace to a smaller size, the one specified, or the minimum size possible.

The shrink operation takes time, and depends on the number of objects and their size in the tablespace. You can run the operation in analyze mode, which is highly recommended. The analysis will give you information about list and size of the objects you can move, the object you cannot move (unsupported objects), and a recommended target size for the tablespace.

This is how shrink_tablespace works:

DBMS_SPACE.SHRINK_TABLESPACE(ts_name, shrink_mode, target_size, shrink_result)

where:
–ts_name is the name of the tablespace
–shrink_mode can be TS_MODE_ANALYZE | TS_MODE_SHRINK | TS_MODE_SHRINK_FORCE (TS_MODE_SHRINK is the default)
–target_size is the desired size of the tablespace in bytes. Default is TS_TARGET_MAX_SHRINK.
–shrink_result is the output of the procedure, returned as a CLOB.

Let’s see how this works with an example. Here is the The Environment Setup:

create bigfile tablespace user_data 
datafile '/opt/oracle/oradata/FREE/FREEPDB1/user_data01.dbf' size 10M 
autoextend on next 10M maxsize 10G;
 
--create 3 tables and 2 indexes and add lots of rows to the tables
 
create table hr.objects1 tablespace user_data 
as select * from dba_objects;
 
create table hr.objects2 tablespace user_data 
as select * from dba_objects;
 
create table hr.objects3 tablespace user_data 
as select * from dba_objects;
 
insert into hr.objects1 select * from dba_objects;
insert into hr.objects2 select * from dba_objects;
insert into hr.objects3 select * from dba_objects;
--repeat the inserts many times, this works as there are no unique keys in the tables
 
--create indexes:
create index hr.obj_name_ix1 on hr.objects1 (object_name) tablespace  user_data;
create index hr.obj_name_ix2 on hr.objects2 (object_name) tablespace  user_data;
 
--size of the tablespace:
 
select dbms_xplan.format_size(bytes) as tbs_size
from dba_data_files where tablespace_name='USER_DATA';
 
TBS_SIZE
-----------
2120M
 
--objects in the tablespace:
 
col owner for A8
col segment_name for A15
col segment_type for A15
col obj_size for A10
 
select owner,segment_name,segment_type,dbms_xplan.format_size(bytes) obj_size 
from dba_segments
where tablespace_name='USER_DATA';
 
OWNER    SEGMENT_NAME  SEG_TYPE  OBJ_SIZE
--------  --------------------  ----------   ----------
HR         OBJECTS1               TABLE        461M
HR         OBJECTS2               TABLE        979M
HR         OBJECTS3               TABLE          39M
HR         OBJ_NAME_IX1       INDEX        148M
HR         OBJ_NAME_IX2       INDEX        318M

Let’s analyze the tablespace with dbms_space.shrink_tablespace. After that we will drop one table and the associated index, and see how the process actually works!

set serveroutput on
execute dbms_space.shrink_tablespace('USER_DATA', shrink_mode => DBMS_SPACE.TS_MODE_ANALYZE);
 
-------------------ANALYZE RESULT-------------------
Total Movable Objects: 0
Total Movable Size(GB): 0
Original Datafile Size(GB): 2.07
Suggested Target Size(GB): 2.07
Process Time: +00 00:00:02.332827
 
PL/SQL procedure successfully completed.
 
SQL> drop table hr.objects2 purge;
 
Table dropped.
 
set serveroutput on
execute dbms_space.shrink_tablespace('USER_DATA', shrink_mode => DBMS_SPACE.TS_MODE_ANALYZE);
 
-------------------ANALYZE RESULT-------------------
Total Movable Objects: 1
Total Movable Size(GB): .14
Original Datafile Size(GB): 2.07
Suggested Target Size(GB): .84
Process Time: +00 00:00:04.533851
 
execute dbms_space.shrink_tablespace('USER_DATA');
 
-------------------SHRINK RESULT-------------------
Total Moved Objects: 1
Total Moved Size(GB): .14
Original Datafile Size(GB): 2.07
New Datafile Size(GB): .71
Process Time: +00 00:00:30.989327
 
PL/SQL procedure successfully completed.

You can see above that analyzing the tablespace takes much less time (4s) vs actually moving the table and shrinking the file (30s).

One thing to note is that the index on the objects1 table is valid after the shrink. Could this means that behind the scene, Oracle is also rebuild the index? Most likely yes! However I think more testing is required to answer that question, perhaps tracing the session.

select index_name, status from dba_indexes where index_name='OBJ_NAME_IX1';
 
INDEX_NAME  STATUS
--------------- --------
OBJ_NAME_IX1    VALID