DBA Hub

📋Steps in this guide1/3

Space Management Enhancements in Oracle Database 21c

This article describes the space management enhancements in Oracle database 21c.

oracle 21cconfigurationintermediate
by OracleDba
33 views
1

SecureFiles Shrink

In previous releases we were only able to free up space from SecureFile lobs by moving them, which could take a considerable amount of time for a large lob segment. In Oracle 21c we can defragment lob segments without affecting access. This releases unused space, without the overhead of a full move of the LOB segment. The shrink can be performed for the lob segments of a specific column, or as part of a cascade operation for a table. The cascade operation was valid in previous releases, but SecureFile LOB segments were not included in the cascade. The view contains a row for a shrink operation of a segment. It is updated during the operation, and is overwritten if another shrink operation is requested for the same segment. If the LOB is shrunk directly, rather than as part of a cascading table shrink, row movement doesn't need to be enabled for the operation to complete, as shown below. We create a table containing a LOB column, and populate it with 1000 rows, We check the lob segment statistics and we can see the number of blocks used to store the LOB. We delete the majority of the rows from the table. When we check the lob segment statistics and we can see the number of blocks used to store the LOB segment hasn't changed. We shrink the lob segment. When we check the lob segment statistics again we see the number of blocks used to store the LOB segment has reduced. We can see row movement was not enabled on this table, yet the shrink of the SecureFile LOB segment worked anyway. Remember, the table rows are not being shrunk by this operation. Just the LOB segments. In Oracle 19c the same shrink operation on a SecureFile LOB would give the following error.

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
alter table tab1 move lob(lob_column_name) store as (tablespace new_ts);

alter table t1 modify lob (colb_column1) (shrink space);
alter table t1 shrink space cascade;

create table t1 as 
  select level as id,
         to_clob(dbms_random.string('x',32767)) as clob_data
  from   dual
  connect by level <= 1000;

commit;

alter table t1 add constraint t1_pk primary key (id);

select ul.table_name,
       ul.column_name,
       ul.segment_name,
       us.blocks
from   user_lobs ul
       join user_segments us on us.segment_name = ul.segment_name;

TABLE_NAME   COLUMN_NAME  SEGMENT_NAME                       BLOCKS
------------ ------------ ------------------------------ ----------
T1           CLOB_DATA    SYS_LOB0000074741C00002$$            2088

1 row selected.

SQL>

delete from t1 where id < 900;
commit;

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


select ul.table_name,
       ul.column_name,
       ul.segment_name,
       us.blocks
from   user_lobs ul
       join user_segments us on us.segment_name = ul.segment_name;

TABLE_NAME   COLUMN_NAME  SEGMENT_NAME                       BLOCKS
------------ ------------ ------------------------------ ----------
T1           CLOB_DATA    SYS_LOB0000074741C00002$$            2088

SQL>

alter table t1 modify lob(clob_data) (shrink space);

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


select ul.table_name,
       ul.column_name,
       ul.segment_name,
       us.blocks
from   user_lobs ul
       join user_segments us on us.segment_name = ul.segment_name;
TABLE_NAME   COLUMN_NAME  SEGMENT_NAME                       BLOCKS
------------ ------------ ------------------------------ ----------
T1           CLOB_DATA    SYS_LOB0000079689C00002$$            1184

SQL>

select row_movement
from   user_tables
where  table_name = 'T1';

ROW_MOVE
--------
DISABLED

SQL>

alter table t1 modify lob(clob_data) (shrink space);

Error starting at line : 1 in command -
alter table t1 modify lob(clob_data) (shrink space)
Error report -
ORA-10635: Invalid segment or tablespace type
2

Automatic Temporary Tablespace Shrink

As the name suggests, the Automatic Temporary Tablespace Shrink feature will shrink the size of the temporary tablespace to free up space. The database can pre-emptively grow the temporary tablespace if more is needed. The documentation refers to this as Automatic Temp Tablespace Sizing. That allows us to let the temporary tablespace expand and contract as needed, without a permanent loss of disk space. At the time of writing the documentation is limited to the New Feature manual ( here ), which just says it exists with no details of usage control or logging. Thanks to Roger MacNicol for pointing out the relevant statistics in the V$SYSSTAT view. This feature was first introduced in Oracle 19c Autonomous Database, but from Oracle 21c it is available on-prem for enterprise edition installations. See the licensing manual here .

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
column name format a40

select con_id,
       name,
       value
from   v$sysstat
where  name like '%TBS%';

    CON_ID NAME                                          VALUE
---------- ---------------------------------------- ----------
         0 TBS Extension: tasks created                      0
         0 TBS Extension: tasks executed                     0
         0 TBS Extension: files extended                     0
         0 TBS Extension: bytes extended                     0
         0 TBS Shrink: tasks created                         0
         0 TBS Shrink: tasks executed                        0

SQL>
3

Automatic Undo Tablespace Shrink

As the name suggests, the Automatic Undo Tablespace Shrink feature will shrink the size of the undo tablespace to free up space. Expired undo segments are dropped, and if possible the data files are shrunk. That allows us to let the undo tablespace expand and contract as needed, without a permanent loss of disk space. At the time of writing the documentation is limited to the New Feature manual ( here ), which just says it exists with no details of usage control or logging. Thanks to Roger MacNicol for pointing out the relevant statistics in the V$SYSSTAT view. According to the licensing manual ( here ) this features is available on Enterprise Edition. This feature was first introduced in Oracle 19c Autonomous Database, but from Oracle 21c it is available on-prem for enterprise edition installations. See the licensing manual here . For more information see: Hope this helps. Regards Tim...

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
column name format a40

select con_id,
       name,
       value
from   v$sysstat
where  name like '%TBS%';

    CON_ID NAME                                          VALUE
---------- ---------------------------------------- ----------
         0 TBS Extension: tasks created                      0
         0 TBS Extension: tasks executed                     0
         0 TBS Extension: files extended                     0
         0 TBS Extension: bytes extended                     0
         0 TBS Shrink: tasks created                         0
         0 TBS Shrink: tasks executed                        0

SQL>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!