Monday, June 10, 2024

Bigfile Tablespace Shrink in Oracle Database 23ai

Oracle Database 23ai, Oracle Database Certification, Oracle Database Prep, Oracle Database Guides, Oracle Database Learning

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

Related Posts

0 comments:

Post a Comment