DBA Hub

📋Steps in this guide1/4

Smallfile Tablespace Shrink in Oracle Database 23ai/26ai (23.7)

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

oracle 23configurationintermediate
by OracleDba
19 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 need to specify smallfile explicitly. Notice we create the tablespace with multiple datafiles. We check the size of the datafiles associated with the tablespace and the tables. Notice the data is spread across the 4 datafiles. 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
76
77
78
79
80
81
82
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 smallfile tablespace reclaim_ts datafile
  size 10m autoextend on next 1m,
  size 10m autoextend on next 1m,
  size 10m autoextend on next 1m,
  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__mtmskcv6_.dbf       124928        976
o1_mf_reclaim__mtmskcvd_.dbf       109312        854
o1_mf_reclaim__mtmskcvl_.dbf        98048        766
o1_mf_reclaim__mtmskcvr_.dbf        98944        773

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             200692 1567.90625
T2             200692 1567.90625

SQL>

truncate table t1;

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

Analyze Smallfile 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 smallfile tablespace name and the shrink mode constant. It shows a potential space saving.

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): .37
Original Datafile Size(GB): 3.29
Suggested Target Size(GB): 1.94
Process Time: +00 00:00:02.036832

PL/SQL procedure successfully completed.

SQL>
3

Shrink Smallfile Tablespace

We run a shrink operation by calling the procedure with the tablespace name. We now have three datafiles, so objects have been moved, but we've freed up much space. The previous command is the equivalent of calling the procedure with a shrink mode of and a target size of . We run this, performing a second shrink operation. Notice we are left with two datafiles. We run it a third time. Based on the file sizes it has moved some objects, but we still have two datafiles. If the aim is to compact this into a single datafile, we may have to run the operation several more times. In this example we had to run the shrink 2 more times before we got a single datafile. The files have been merged, but we would expect about half of the blocks to have been saved by the shrink tablespace operation. We have in fact not saved much space at all.

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
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
set serveroutput on
execute dbms_space.shrink_tablespace('RECLAIM_TS');
-------------------SHRINK RESULT-------------------
Total Moved Objects: 2
Total Moved Size(GB): .37
Original Datafile Size(GB): 3.29
New Datafile Size(GB): 2.76
Process Time: +00 00:00:52.268145

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__mtmskcv6_.dbf       206592       1614
o1_mf_reclaim__mtmskcvd_.dbf        57472        449
o1_mf_reclaim__mtmskcvl_.dbf        97152        759

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);
-------------------SHRINK RESULT-------------------
Total Moved Objects: 2
Total Moved Size(GB): .33
Original Datafile Size(GB): 2.76
New Datafile Size(GB): 2.93
Process Time: +00 00:00:42.779902

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__mtmskcv6_.dbf       296704       2318
o1_mf_reclaim__mtmskcvl_.dbf        87168        681

SQL>

set serveroutput on
execute dbms_space.shrink_tablespace('RECLAIM_TS');
-------------------SHRINK RESULT-------------------
Total Moved Objects: 2
Total Moved Size(GB): .38
Original Datafile Size(GB): 2.93
New Datafile Size(GB): 3.09
Process Time: +00 00:00:42.502616

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__mtmskcv6_.dbf       371840       2905
o1_mf_reclaim__mtmskcvl_.dbf        32896        257

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__mtmskcv6_.dbf       382080       2985

SQL>
4

Additional Information

Here is some additional information about shrinking tablespaces. - Unlike the bigfile tablespace shrink, the smallfile tablespace shrink doesn't appear to release much free space. I was so focussed on the file merging, I didn't actually notice the number of blocks was not reducing. Thanks to Jonathan Lewis for pointing that out to me. - 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 a Tablespace - DBMS_SPACE - Bigfile Tablespace Shrink in Oracle Database 23ai/26ai Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!