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

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);

Source: oracle-base.com