When discussing 23ai features with my customers, I’ve noticed that DBAs show particular interest in the newly introduced tablespace shrink functionality for Oracle tablespaces. They are eager to learn about the capabalities and when and how they can be used.
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 23ai, you can now reorganize tablespaces both SMALLFILE oder 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 23ai, 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.
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
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 10000
SQL> variable result clob
SQL> col result format a200
SQL> execute DBMS_SPACE.SHRINK_TABLESPACE('RECLAIMTS', 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: RECLAIMTS }
2. { RECLAIM_USER.T2_PK | type: INDEX | blocks: 32 | tablespace_name: RECLAIMTS }
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
You may also find useful information in the Database Administrator’s Guide in chapter “Shrinking a bigfile tablespace“. In addition Tim Hall described the feature in his posting Bigfile Tablespace Shrink in Oracle Database 23ai.
Using Tim’s simple setup as a guide, let’s look “under the hook” for additional context and examples. 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. We’ll wrap up the post with some more details about the feature based on my initial testing experience.
Setup and example
You can also repeat the following example in the available 23ai database FREE version or in Oracle 23ai Database Cloud, on Exadata or ODA.
Let’s connect to one PDB and create a BIGFILE tablespace named RECLAIMTS. The new user RECLAIM_USER will execute the user commands.
SQL> drop tablespace reclaimts including contents and datafiles; Tablespace dropped. SQL> drop user reclaim_user; User dropped. SQL> create bigfile tablespace reclaimts datafile size 10M autoextend on next 5M; Tablespace created. SQL> create user reclaim_user identified by reclaim default tablespace reclaimts quota unlimited on reclaimts; 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 = 'RECLAIMTS';
TABLESPACE_NAME BLOCKS SIZE_MB
------------------------- ---------- ----------
RECLAIMTS 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='RECLAIMTS' group by ts.tablespace_name; TABLESPACE_NAME MB_FREE FRAGMENTS BIGGEST ------------------------- ---------- ---------- ---------- RECLAIMTS 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 = 'RECLAIMTS'
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 = 'RECLAIMTS'
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 RECLAIMTS 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('RECLAIMTS', 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: RECLAIMTS }
2. { RECLAIM_USER.T2_PK | type: INDEX | blocks: 32 | tablespace_name: RECLAIMTS }
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('RECLAIMTS');
-------------------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='RECLAIMTS' group by ts.tablespace_name; TABLESPACE_NAME MB_FREE FRAGMENTS BIGGEST ------------------------- ---------- ---------- ---------- RECLAIMTS 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 = 'RECLAIMTS'
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 = 'RECLAIMTS'
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('RECLAIMTS', 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: RECLAIMTS }
2. { RECLAIM_USER.T2_PK | type: INDEX | blocks: 32 | tablespace_name: RECLAIMTS }
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('RECLAIMTS');
-------------------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='RECLAIMTS' group by ts.tablespace_name; TABLESPACE_NAME MB_FREE FRAGMENTS BIGGEST -------------------- ---------- ---------- ---------- RECLAIMTS 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 = 'RECLAIMTS'
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 = 'RECLAIMTS' 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('RECLAIMTS', 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('RECLAIMTS');
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.
Links to more information
- Posting Bigfile Tablespace Shrink in Oracle Database 23ai (Tim Hall)
- Database Administrator’s Guide in chapter “Shrinking a tablespace“
- PL/SQL Packages and Types Reference DBMS_SPACE