Tablespace Shrink in 26ai
Background:
Traditionally in Oracle, shrinking is possible at:
- Segment level → ALTER TABLE ... SHRINK SPACE, ALTER INDEX ... SHRINK SPACE
- Datafile level → ALTER DATABASE DATAFILE ... RESIZE
- Export & Re-Import → Expdp and Impdp
- Movement: → Move into another TBS.
But shrinking an entire tablespace automatically was not directly possible until recent improvements in Oracle 26c/26ai, where DBMS_SPACE has been extended.
In this post, I will explain 26ai's features regarding table space shrinking capability for Oracle tablespaces.
A first use case is illustrated in the following scenario:
Imagine that database objects have been removed, resulting in unused space within the tablespace. In such situations, you may wish to reduce the actual data file size to free up space on your disks. With 26ai, you can now reorganize tablespaces both SMALLFILE or BIGFILE tablespaces while resizing the corresponding data file(s) This is done by moving objects within the tablespace, which can be done either online or offline.
Just a reminder:
A bigfile tablespace is defined as a tablespace that has one large data file (up to 4G blocks). Traditional smallfile tablespaces, in contrast, can contain multiple data files, but the files cannot be as large. And starting with Oracle Database 26ai, the BIGFILE functionality is the default for SYSAUX, SYSTEM, and USER tablespaces.
You can use the SHRINK_TABLESPACE procedure from the DBMS_SPACE package to resize both BIGFILE and SMALLFILE tablespaces or analyze them prior to resizing. This procedure utilizes online DDL to reorganize the objects within the data file, and after the objects have been successfully reorganized, it executes a resize of the data file.
The usage is quite simple: You only need to specify the tablespace name and the shrink mode such as
- TS_MODE_ANALYZE for an analyze mode
- TS_MODE_SHRINK, the default
- TS_MODE_SHRINK_FORCE, this will move objects online by default, but if the online move fails, it will attempt to move them offline.
The following code snippet illustrates the arguments of the new procedure. A complete description can be found in the PL/SQL Packages and Types Reference DBMS_SPACE.
DBMS_SPACE.SHRINK_TABLESPACE
- Allows you to shrink free space inside a tablespace automatically, instead of manually resizing each datafile.
- Internally, Oracle will move segments toward the beginning of the datafile(s), release unused extents, and then reduce the size.
PROCEDURE SHRINK_TABLESPACE
Argument Name Type In/Out Default?
---------------------- -------------- ------------ --------
TS_NAME VARCHAR2 IN
SHRINK_MODE NUMBER IN DEFAULT
TARGET_SIZE NUMBER IN DEFAULT
SHRINK_RESULT CLOB OUT
SHRINK_TABLESPACE Procedure Parameters
Parameter | Description |
ts_name | The name of the tablespace to be analyzed or resized |
shrink_mode | The shrink mode to execute. The values are: • TS_SHRINK_MODE_ANALYZE • TS_SHRINK_MODE_ONLINE • TS_SHRINK_MODE_AUTO • TS_SHRINK_MODE_OFFLINE The default mode is TS_SHRINK_MODE_ONLINE which moves objects online by default, except for index-organized tables. TS_SHRINK_MODE_AUTO will move objects online by default, but if the online move fails, it will attempt to move them offline. TS_SHRINK_MODE_OFFLINE offers the best shrink outcome and performance. |
target_size | The desired tablespace size specified in bytes. The default value is TS_TARGET_MAX_SHRINK. |
shrink_result | Output result of the procedure returned as a CLOB. The output results for TS_SHRINK_MODE_ONLINE include: • total number and size of moved objects • original and new datafile size • process time The output results for TS_SHRINK_MODE_ANALYZE include: • list of movable objects • total number and size of movable objects • suggested target size • process time |
The following code snippet shows a first example.
The end result is a CLOB and you may view it e.g. in SQL*Plus.
SQL> set long 20000
SQL> variable result clob
SQL> col result format a200
SQL> execute DBMS_SPACE.SHRINK_TABLESPACE ('TEST_TBS', SHRINK_MODE => DBMS_SPACE.TS_MODE_ANALYZE, SHRINK_RESULT =>: result);
PL/SQL procedure successfully completed.
SQL> print result
RESULT
----------------------------------------------------------------------------------
Movable Objects:
1. {RECLAIM_USER.T2 | type: TABLE | blocks: 20480 | tablespace_name: TEST_TBS}
2. {RECLAIM_USER.T2_PK | type: INDEX | blocks: 32 | tablespace_name: TEST_TBS}
Total Movable Objects: 2
Total Movable Size (GB): .16
Original Datafile Size (GB): 1.01
Suggested Target Size (GB): .38
Process Time: +00 00:00:01.669704
Data dictionary views such as DBA_FREE_SPACE and USER_SEGMENTS and the "Tablespace Content" View from Oracle Enterprise Manager Cloud control will be used to demonstrate the process.
Setup and example
You can also repeat the following example in the available 26ai database FREE version or in Oracle 26ai Database Cloud, on Exadata or ODA.
Let's connect to one PDB and create a BIGFILE tablespace named TEST_TBS. The new user RECLAIM_USER will execute the user commands.
SQL> drop tablespace TEST_TBS including contents and datafiles;
Tablespace dropped.
SQL> drop user reclaim_user;
User dropped.
SQL> create bigfile tablespace TEST_TBS datafile size 10M autoextend on next 5M;
Tablespace created.
SQL> create user reclaim_user identified by reclaim default tablespace TEST_TBS quota unlimited on TEST_TBS;
User created.
SQL> grant connect, resource, dba to reclaim_user;
Grant succeeded.
Connect to user RECLAIM_USER and create the following 2 tables. Then insert some rows.
SQL> connect reclaim_user/reclaim@<servicename>
SQL> create table t1 (
id number,
col1 varchar2(4000),
col2 varchar2(4000),
constraint t1_pk primary key (id));
SQL> create table t2 (
id number,
col1 varchar2(4000),
col2 varchar2(4000),
constraint t2_pk primary key (id));
SQL> insert /*+append*/ into t1 select rownum, rpad('x', 4000, 'x'), rpad('x', 4000, 'x')
from dual connect by level <= 10000;
10000 rows created.
SQL> commit;
SQL> insert /*+append*/ into t2 select rownum, rpad('x', 4000, 'x'), rpad('x', 4000, 'x')
from dual connect by level <= 10000;
10000 rows created.
SQL> commit;
Let's generate some statistics.
SQL> exec dbms_stats.gather_schema_stats('RECLAIM_USER');
PL/SQL procedure successfully completed.
Check the size now in DBA_DATA_FILES and USER_SEGMENTS.
SQL> col tablespace_name format a25
SQl> select tablespace_name, blocks, bytes/1024/1024 as size_mb
from dba_data_files where tablespace_name = 'TEST_TBS';
TABLESPACE_NAME BLOCKS SIZE_MB
------------------------- ---------- ----------
TEST_TBS 132480 1035
SQL> col segment_name format a10
SQL> select segment_name, segment_type, bytes/1024 size_kb from user_segments;
SEGMENT_NA SEGMENT_TY SIZE_KB
---------- ---------- ----------
T1 TABLE 163840
T2 TABLE 163840
T1_PK INDEX 256
T2_PK INDEX 256
Let's truncate table T1 and collect statistics:
SQL> truncate table t1;
Table truncated.
SQL> exec dbms_stats.gather_schema_stats('RECLAIM_USER');
PL/SQL procedure successfully completed.
Let's examine the space - first the segment space. Obviously T1 is truncated.
SQL> col segment_name format a10
SQL> select segment_name, segment_type, bytes/1024 size_kb from user_segments;
SEGMENT_NA SEGMENT_TY SIZE_KB
---------- ---------- ----------
T1 TABLE 64
T1_PK INDEX 64
T2 TABLE 163840
T2_PK INDEX 256
Let's now investigate the space within the tablespace. Five free fragments are visible, and these could be potential locations for moving objects. The largest one is around 646 MB in size.
SQL> select ts.tablespace_name, sum (nvl(fs. bytes,0))/1024/1024 as MB_FREE,
count (*) as FRAGMENTS, max (nvl(fs. bytes,0))/1024/1024 as BIGGEST
from user_free_space fs, user_tablespaces ts
where fs.tablespace_name(+) = ts.tablespace_name and ts.tablespace_name='TEST_TBS'
group by ts.tablespace_name;
TABLESPACE_NAME MB_FREE FRAGMENTS BIGGEST
------------------------- ---------- ---------- ----------
TEST_TBS 806.5625 5 646
To get the full picture, we could use the following query to show the mapping of the segments in the tablespace.
SQL> col segment_type format a10
SQL> set pagesize 100
SQl> select block_id begin_block, block_id + blocks - 1 end_block, blocks, bytes/1024/1024 MB,
segment_name, segment_type
from dba_extents where tablespace_name = 'TEST_TBS'
union all
select block_id begin_block, block_id + blocks - 1 end_block, blocks, bytes/1024/1024 MB,
'free' segment_name, null segment_type
from dba_free_space
where tablespace_name = 'TEST_TBS'
order by 1 desc;
BEGIN_BLOCK END_BLOCK BLOCKS MB SEGMENT_NA SEGMENT_TY
----------- ---------- ---------- ---------- ---------- ----------
49792 132479 82688 646 free <== first free space starting at Block id 49792
48768 49791 1024 8 T2 TABLE
47744 48767 1024 8 T2 TABLE
46720 47743 1024 8 T2 TABLE
45696 46719 1024 8 T2 TABLE
44672 45695 1024 8 T2 TABLE
43648 44671 1024 8 T2 TABLE
42624 43647 1024 8 T2 TABLE
41600 42623 1024 8 T2 TABLE
40576 41599 1024 8 T2 TABLE
39552 40575 1024 8 T2 TABLE
...
29312 29319 8 .0625 T2 TABLE
9216 29311 20096 157 free <== another notable free space starting with 9216
1024 1279 256 2 free
1016 1023 8 .0625 T2 TABLE
1008 1015 8 .0625 T2 TABLE
...
Let's use Oracle Enterprise Manager Cloud Control to display the tablespace content.
Go to Administration => Storage=> Tablespace, click on tablespace TEST_TBS and choose "Show Tablespace Content" in the menu on the right. Then you will get a new page with the Extent Map you can expand. Notice the free space is always colored in green.
Shrinking Tablespace
What is the possible outcome of the shrink run now? You can get a list of objects by checking SHRINK_RESULT returned. It will list the objects that are not supported such as cluster tables, some advanced queuing tables etc.
SQL> set linesize window
SQL> variable result clob
SQL> col result format a200
SQL> execute DBMS_SPACE.SHRINK_TABLESPACE('TEST_TBS', SHRINK_MODE => DBMS_SPACE.TS_MODE_ANALYZE, SHRINK_RESULT =>: result);
PL/SQL procedure successfully completed.
SQL> print result
RESULT
---------------------------------------------
Movable Objects:
1. {RECLAIM_USER.T2 | type: TABLE | blocks: 20480 | tablespace_name: TEST_TBS}
2. {RECLAIM_USER.T2_PK | type: INDEX | blocks: 32 | tablespace_name: TEST_TBS}
Total Movable Objects: 2
Total Movable Size (GB): .16
Original Datafile Size (GB): 1.01
Suggested Target Size (GB): .38
Process Time: +00 00:00:01.669704
The table T2 and the index T2_PK are the two objects that can be moved. The data file size should then be approximately 0.38 GB after that. When checking out the Extent Map above, we could see that there are at least two bigger empty pieces which could be used for that - one starts with block id 9216 and one with block id 49792.
So let's shrink the tablespace with SHRINK_TABLESPACE and the default setting to achieve the maximum possible shrink result. You can use a specific TARGET_SIZE to reach your own shrink size goal (see example at the end of the posting).
SQL> set serveroutput on
SQL> execute dbms_space.shrink_tablespace('TEST_TBS');
-----------SHRINK RESULT--------------
Total Moved Objects: 2
Total Moved Size (GB): .16
Original Datafile Size (GB): 1.01
New Datafile Size (GB): .39
Process Time: +00 00:00:10.611905
PL/SQL procedure successfully completed.
Let's do the same checks as before. There are still 5 fragments but the free space is less than before.
SQL> select ts.tablespace_name, sum (nvl(fs. bytes,0))/1024/1024 as MB_FREE,
count (*) as FRAGMENTS, max (nvl(fs. bytes,0))/1024/1024 as BIGGEST
from user_free_space fs, user_tablespaces ts
where fs.tablespace_name(+) = ts.tablespace_name and ts.tablespace_name='TEST_TBS'
group by ts.tablespace_name;
TABLESPACE_NAME MB_FREE FRAGMENTS BIGGEST
-------------- ---------- ---------- ----------
TEST_TBS 168.5625 5 167
Let's get some more details.
SQL> col segment_type format a10
SQL> set pagesize 100
SQl> select block_id begin_block, block_id + blocks - 1 end_block, blocks, bytes/1024/1024 MB,
segment_name, segment_type
from dba_extents
where tablespace_name = 'TEST_TBS'
union all
select block_id begin_block, block_id + blocks - 1 end_block, blocks, bytes/1024/1024 MB,
'free' segment_name, null segment_type
from dba_free_space
where tablespace_name = 'TEST_TBS'
order by 1 desc;
BEGIN_BLOCK END_BLOCK BLOCKS MB SEGMENT_NA SEGMENT_TY
----------- ---------- ---------- ---------- ---------- ----------
49792 50815 1024 8 T2 TABLE
28416 49791 21376 167 free <== could be used for a segment move
27392 28415 1024 8 T2 TABLE
26368 27391 1024 8 T2 TABLE
25344 26367 1024 8 T2 TABLE
24320 25343 1024 8 T2 TABLE
23296 24319 1024 8 T2 TABLE
22272 23295 1024 8 T2 TABLE
21248 22271 1024 8 T2 TABLE
20224 21247 1024 8 T2 TABLE
19200 20223 1024 8 T2 TABLE
...
1056 1151 96 .75 free <= free space
1048 1055 8 .0625 T2_PK INDEX
1040 1047 8 .0625 T2_PK INDEX
...
Let's check the Extent Map.
So, let's analyze again. There is still some possible savings as we can see from the Extent Map above and the analyze command confirms that.
SQL> variable result clob
SQL> col result format a200
SQL> execute DBMS_SPACE.SHRINK_TABLESPACE('TEST_TBS', SHRINK_MODE => DBMS_SPACE.TS_MODE_ANALYZE, SHRINK_RESULT =>: result);
SQL> print result
RESULT
-------------------------------------------
Movable Objects:
1. {RECLAIM_USER.T2 | type: TABLE | blocks: 20480 | tablespace_name: TEST_TBS}
2. {RECLAIM_USER.T2_PK | type: INDEX | blocks: 32 | tablespace_name: TEST_TBS}
Total Movable Objects: 2
Total Movable Size (GB): .16
Original Datafile Size (GB): .39
Suggested Target Size (GB): .38
Process Time: +00 00:00:01.674684
Therefore, let's shrink again.
SQL> set serveroutput on;
SQL> execute dbms_space.shrink_tablespace('TEST_TBS');
--------SHRINK RESULT-----------
Total Moved Objects: 2
Total Moved Size (GB): .16
Original Datafile Size (GB): .39
New Datafile Size (GB): .37
Process Time: +00 00:00:11.726847
PL/SQL procedure successfully completed.
Let's check the result.
SQL> select ts.tablespace_name, sum (nvl(fs. bytes,0))/1024/1024 as MB_FREE,
count (*) as FRAGMENTS, max (nvl(fs. bytes,0))/1024/1024 as BIGGEST
from user_free_space fs, user_tablespaces ts
where fs.tablespace_name(+) = ts.tablespace_name and ts.tablespace_name='TEST_TBS'
group by ts.tablespace_name;
TABLESPACE_NAME MB_FREE FRAGMENTS BIGGEST
-------- -------- --------- ----------
TEST_TBS 152.5625 5 150
Let's check the Extent Map. We can see that fragments are moved again.
SQL> select block_id begin_block, block_id + blocks - 1 end_block, blocks, bytes/1024/1024 MB, segment_name, segment_type
from dba_extents where tablespace_name = 'TEST_TBS'
union all
select block_id begin_block, block_id + blocks - 1 end_block, blocks, bytes/1024/1024 MB,
'free' segment_name, null segment_type
from dba_free_space where tablespace_name = 'TEST_TBS' order by 1 desc;
BEGIN_BLOCK END_BLOCK BLOCKS MB SEGMENT_NA SEGMENT_TYPE
----------- ---------- ---------- ---------- ---------- --------------------
47744 48767 1024 8 T2 TABLE
46720 47743 1024 8 T2 TABLE
45696 46719 1024 8 T2 TABLE
44672 45695 1024 8 T2 TABLE
43648 44671 1024 8 T2 TABLE
42624 43647 1024 8 T2 TABLE
41600 42623 1024 8 T2 TABLE
...
28416 28543 128 1 T2 TABLE
9216 28415 19200 150 free <=
first free space...
And the Extent Map in Cloud Control.
Summary and Additional Considerations
With the new procedure SHRINK_TABLESPACE you can reorganize tablespaces and resize the associated data file(s) to its smallest possible size by moving objects within the tablespace, either online or offline. You only need to use one command for this. Please note: When shrinking a smallfile tablespace, the data file sizes may increase or decrease depending on the size of the objects placed in the data files.
As mentioned above you can use DBMS_SPACE.SHRINK_TABLESPACE in different ways:
- Analyze a tablespace for a suggested target size.
- Resize a tablespace to its minimum possible size.
- Attempt to resize a tablespace to a specified target size.
Before trying to shrink, you should use the analyze command to find out the potential of a shrink operation. It will take much less time that actually shrinking it. The result of this analysis contains useful information including a list of movable objects, the list of unsupported objects, the total size of movable objects in the tablespace, and the suggested target size for the tablespace.
The shrink operation itself will reorganize the tablespace which will take some time. If you want to monitor long-running executions you may use V$SESSION_LONGOPS. OPNAME "Tablespace Shrink" defines our tablespace shrink operation.
SQL> select opname, sofar, time_remaining,
to_char(start_time,'dd.mm.yyyy-HH24:MI:SS') StartTime, message
from v$session_longops
where time_remaining>=0 and opname like '%Table%' order by 4;
OPNAME
-------------------------------------------------
SOFAR TIME_REMAINING STARTTIME
---------- -------------- --------------------
MESSAGE
-------------------------------------------------
Online Move Table
9 0 22.09.2024-19:44:04
Online Move Table: T2 : 9 out of 9 Steps done
Transform Heap Mapping Table to IOT Mapping Table
1 0 22.09.2024-19:44:10
Transform Heap Mapping Table to IOT Mapping Table: T2 : 1 out of 1 Step done
Wait for DMLs - Swap dobj# - Move Table Online
1 0 22.09.2024-19:44:12
Wait for DMLs - Swap dobj# - Move Table Online: T2 : 1 out of 1 Step done
Tablespace Shrink
1 0 22.09.2024-19:44:14
Tablespace Shrink: segment: 1 out of 1 segments done
Tablespace Shrink
1 0 22.09.2024-19:44:19
Tablespace Shrink: segment: 1 out of 1 segments done
...
Here is an example how to use SHRINK_TABLESPACE with a specific TARGET_SIZE in bytes. It will always use and adjust the target size that is required to complete the operation successfully.
SQL> set serveroutput on
SQL> execute dbms_space.shrink_tablespace('TEST_TBS', target_size => 500000000);
Specified target size is too small, adjusting it to 1395654656
------------SHRINK RESULT-------------
Total Moved Objects: 1
Total Moved Size (GB): 0
Original Datafile Size (GB): 2.01
New Datafile Size (GB): 1.3
Process Time: +00 00:00:06.728767
PL/SQL procedure successfully completed.
Partially failing DBMS_SPACE.SHRINK_TABLESPACE is possible. Nevertheless, if a move DDL fails, the command always reports the cause. The command still resizes the data file to a smaller size if it already successfully moved some objects.
For instance, in the following example it is not possible to move table T2 because a lock is held in another session.
SQL> set serveroutput on
SQL> execute dbms_space.shrink_tablespace('TEST_TBS');
Procedure exited because it can't move an object: Failed Move DDL: alter table "RECLAIM_USER"."T2" move online
Failed Reason: ORA-00054: Failed to acquire a lock (Type: "TM", Name: "DML", Description: "Synchronizes accesses to an object")
because it is currently held by another session. The resource being locked can be identified by 132917 ("Table") and 0 ("operation")
Another option is DBMS_SPACE.TS_MODE_SHRINK_FORCE, which is useful when moving an object offline is possible but not when moving it online. DMLs and queries will be blocked by an offline move. Because some objects cannot be moved online or offline, this mode won't always work.
In any case, my initial testing experience showed that using the Tablespace Shrink feature during a maintenance window produced the best results for me. Therefore, I would advise using a dedicated window for that.
Note
- If you shrink a bigfile tablespace that has autoextend disabled, there will be minimal or no free space left for new objects or data. You must manually resize the tablespace to accommodate any new objects or data, or enable autoextend.
- It is possible for DBMS_SPACE.SRHRNK_TABLESPACE to partially fail. The command always reports the reason if a move DDL failed, but the command still resizes the datafile to a smaller size if it already successufully moved some objects.
- DBMS_SPACE.SHRINK_TABLESPACE('TBS_1') is equivalent to DBMS_SPACE.SHRINK_TABLESPACE('TBS_1', SHRINK_MODE => DBMS_SPACE.TS_SHRINK_MODE_ONLINE, TARGET_SIZE => TS_TARGET_MAX_SHRINK). TS_TARGET_MAX_SHRINK means the target size will be automatically set based on tablespace usage (target size = sum of all object size + buffer). However, it is the best effort to shrink to the target size, and final size may be different than the target size.
- DBMS_SPACE.TS_SHRINK_MODE_AUTO mode can be used if an object can’t be moved online but can be moved offline, and offline move is acceptable. An offline move will block DMLs and queries. This mode won’t always succeed because some objects can’t be moved either online or offline.
- See Restrictions on the ONLINE Clause in Oracle Database SQL Language Reference for objects that cannot be moved online.
- The following objects cannot be moved offline:
1. tables with a LONG datatype
2. cluster tables
3. tables with reservable columns
- DBMS_SPACE.TS_SHRINK_MODE_ANALYZE mode will only do space related estimation, but it can’t predict the success or failure of actual shrink. However, you can get a list of unsupported objects by checking SHRINK_RESULT returned by DBMS_SPACE.SRHINK_TABLESPACE('TBS_1', SHRINK_MODE => DBMS_SPACE.TS_SHRINK_MODE_ANALYZE, SHRINK_RESULT => :result). Currently, the unsupported object list includes cluster tables and some advanced queueing tables.
- It is possible to shrink the SYSAUX tablespace.
Restrictions on the ONLINE Clause
The ONLINE clause is subject to the following restrictions when moving table partitions:
- You cannot specify the ONLINE clause for tables owned by SYS.
- You cannot specify the ONLINE clause for index-organized tables.
- You cannot specify the ONLINE clause for heap-organized tables that contain object types or on which bitmap join indexes or domain indexes are defined.
- Parallel DML and direct path INSERT operations require an exclusive lock on the table. Therefore, these operations are not supported concurrently with an ongoing online partition MOVE, due to conflicting locks.
Things to Note
- Shrink is online in 26ai (no downtime for most operations).
- May involve row movement and segment compaction.
- Works best with locally managed tablespaces and auto segment space management (ASSM).
- Always check reclaimable space with ...ESTIMATE before shrinking.
Conclusion:
Oracle 26ai introduces DBMS_SPACE.SHRINK_TABLESPACE and related APIs to shrink tablespaces and reclaim space more easily and automatically than before.
*********************************
