This article describes the space management enhancements in Oracle database 21c.
◉ 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.
alter table tab1 move lob(lob_column_name) store as (tablespace new_ts);
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.
alter table t1 modify lob (colb_column1) (shrink space);
alter table t1 shrink space cascade;
The cascade operation was valid in previous releases, but SecureFile LOB segments were not included in the cascade.
The V$SECUREFILE_SHRINK 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,
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);
We check the lob segment statistics and we can see the number of blocks used to store the LOB.
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>
We delete the majority of the rows from the table.
delete from t1 where id < 900;
commit;
When we check the lob segment statistics and we can see the number of blocks used to store the LOB segment hasn't changed.
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>
We shrink the lob segment.
alter table t1 modify lob(clob_data) (shrink space);
When we check the lob segment statistics again we see the number of blocks used to store the LOB segment has reduced.
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>
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.
select row_movement
from user_tables
where table_name = 'T1';
ROW_MOVE
--------
DISABLED
SQL>
In Oracle 19c the same shrink operation on a SecureFile LOB would give the following error.
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
◉ 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, 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.
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>
This feature was first introduced in Oracle 19c Autonomous Database, but from Oracle 21c it is available on-prem for enterprise edition installations.
◉ 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.
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>
Source: oracle-base.com
Posts
0 comments:
Post a Comment