DBA Hub

📋Steps in this guide1/4

Bigfile Tablespace Shrink in Oracle Database 23ai/26ai

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

oracle 23configurationintermediate
by OracleDba
22 views
1

Setup

We need a tablespace to run some tests. In Oracle database 23ai/26ai the default file size for a tablespace is bigfile, so we don't need to specify it explicitly. We check the size of the datafile associated with the tablespace and the tables. We truncate the first table, leaving a gap in the datafile before the table segments start. We can repeat this setup between tests to start cleanly.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
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');

column file_name format a30

select substr(file_name, -28) as file_name, blocks, bytes/1024/1024 as size_mb
from   dba_data_files
where  tablespace_name = 'RECLAIM_TS';

FILE_NAME                          BLOCKS    SIZE_MB
------------------------------ ---------- ----------
o1_mf_reclaim__mtn1tkxs_.dbf       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>

truncate table t1;

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

Analyze Bigfile Tablespace

We run an analyze to see how much space we can save by performing a shrink. We call the procedure in the package, passing in the name of the bigfile tablespace name and the shrink mode constant. 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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.26
Suggested Target Size(GB): 3.19
Process Time: +00 00:00:00.950648

PL/SQL procedure successfully completed.

SQL>
3

Shrink Bigfile Tablespace

We run a shrink operation by calling the procedure with the tablespace name. 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 and a target size of .

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
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:17.782739

PL/SQL procedure successfully completed.

SQL>

column file_name format a30

select substr(file_name, -28) as file_name, blocks, bytes/1024/1024 as size_mb
from   dba_data_files
where  tablespace_name = 'RECLAIM_TS';

FILE_NAME                          BLOCKS    SIZE_MB
------------------------------ ---------- ----------
o1_mf_reclaim__mtn1tkxs_.dbf       214016       1672

SQL>

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

Additional Information

Here is some additional information about shrinking 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 don't have all of the restrictions associated with a conventional , despite what the documentation says. The analyze phase will indicate if there are unsupported objects. - The shrink mode of 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 procedure that includes a out parameter, so the result of the operation can be returned as a CLOB, rather than being pushed out using . For more information see: - Shrinking Tablespace - DBMS_SPACE - Smallfile Tablespace Shrink in Oracle Database 23ai (23.7) Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!