8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Bigfile Tablespace Shrink in Oracle Database 23ai

From Oracle database 23ai onward we can use the DBMS_SPACE package to shrink a bigfile tablespace to reclaim unused space.

Setup
Analyze Bigfile Tablespace
Shrink Bigfile Tablespace
Additional Information

Setup

We need a tablespace to run some tests. In Oracle database 23ai the default file size for a tablespace is bigfile, so we don't need to specify it explicitly.

conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba

-- Create a tablespace and user for the test.
drop user if exists reclaim_user cascade;
drop tablespace if exists reclaim_ts including contents and datafiles;

create tablespace reclaim_ts datafile size 10m autoextend on next 1m;

create user reclaim_user identified by reclaim_user default tablespace reclaim_ts quota unlimited on reclaim_ts;
grant create session, create table to reclaim_user;
grant select_catalog_role to reclaim_user;


-- Create and populate two tables in the test schema.
conn reclaim_user/reclaim_user@//localhost:1521/freepdb1

create table t1 (
  id   number,
  col1 varchar2(4000),
  col2 varchar2(4000),
  constraint t1_pk primary key (id)
);

create table t2 (
  id   number,
  col1 varchar2(4000),
  col2 varchar2(4000),
  constraint t2_pk primary key (id)
);

insert /*+append*/ into t1
select rownum, rpad('x', 4000, 'x'), rpad('x', 4000, 'x')
from dual
connect by level <= 100000;
commit;

insert /*+append*/ into t2
select rownum, rpad('x', 4000, 'x'), rpad('x', 4000, 'x')
from dual
connect by level <= 100000;
commit;

exec dbms_stats.gather_table_stats(null, 't1');
exec dbms_stats.gather_table_stats(null, 't2');

We check the size of the datafile associated with the tablespace and the tables.

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

TABLESPACE_NAME                    BLOCKS    SIZE_MB
------------------------------ ---------- ----------
RECLAIM_TS                         427520       3340

SQL>


column table_name format a10

select table_name, blocks, (blocks*8)/1024 as size_mb
from   user_tables
where  table_name in ('T1', 'T2')
order by 1;

TABLE_NAME     BLOCKS    SIZE_MB
---------- ---------- ----------
T1             200696  1567.9375
T2             200694 1567.92188

SQL>

We truncate the first table, leaving a gap in the datafile before the table segments start.

truncate table t1;

exec dbms_stats.gather_table_stats(null, 't1');

We can repeat this setup between tests to start cleanly.

Analyze Bigfile Tablespace

We run an analyze to see how much space we can save by performing a shrink. We call the SHRINK_SPACE procedure in the DBMS_SPACE package, passing in the name of the bigfile tablespace name and the TS_MODE_ANALYZE shrink mode constant.

conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba

set serveroutput on
execute dbms_space.shrink_tablespace('RECLAIM_TS', shrink_mode => dbms_space.ts_mode_analyze);
-------------------ANALYZE RESULT-------------------
Total Movable Objects: 2
Total Movable Size(GB): 1.56
Original Datafile Size(GB): 3.39
Suggested Target Size(GB): 3.19
Process Time: +00 00:00:00.053777

PL/SQL procedure successfully completed.

SQL>

It doesn't think we can save much space, which sounds suspicious as we have truncated one table, which takes up approximately half of the space in the data file.

Shrink Bigfile Tablespace

We run a shrink operation by calling the SHRINK_SPACE procedure with the tablespace name.

set serveroutput on
execute dbms_space.shrink_tablespace('RECLAIM_TS');
-------------------SHRINK RESULT-------------------
Total Moved Objects: 2
Total Moved Size(GB): 1.56
Original Datafile Size(GB): 3.26
New Datafile Size(GB): 1.63
Process Time: +00 00:00:30.586722

PL/SQL procedure successfully completed.

SQL>

Despite what the analyze said, we have reduced the associated datafile to approximately half its original size.

The previous command is the equivalent of calling the procedure with a shrink mode of TS_MODE_SHRINK and a target size of TS_TARGET_MAX_SHRINK.

set serveroutput on
execute dbms_space.shrink_tablespace('RECLAIM_TS', shrink_mode => dbms_space.ts_mode_shrink, target_size => dbms_space.ts_target_max_shrink);

Additional Information

Here is some additional information about shrinking bigfile tablespaces.

Objects are moved to compact the segments in the datafile, so all unused space is at the end of the datafile. This allows the datafile to be shrunk to reclaim the unused space.
Online moves via SHRINK_SPACE don't have all of the restrictions associated with a conventional ALTER TABLE ... MOVE, despite what the documentation says. The analyze phase will indicate if there are unsupported objects.
The shrink mode of TS_MODE_SHRINK_FORCE will do an offline move for objects that don't support online moves. Don't use this option if an offline move will cause a problem in your application.
If the tablespace is not set to autoextend, there will be no room for segments to grow at the end of the operation. You will need to resize the tablespace manually to make room.
A shrink can fail, but it may still reduce the size of the datafile if any moves completed successfully.
We can shrink the SYSAUX tablespace.
There is an overload of the SHRINK_TABLESPACE procedure that includes a SHRINK_RESULT out parameter, so the result of the operation can be returned as a CLOB, rather than being pushed out using DBMS_OUTPUT.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.