8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Space Management Enhancements in Oracle Database 21c
This article describes the space management enhancements in Oracle database 21c.
For more information see:
- SecureFiles Shrink in Oracle 21c
- Online Segment Shrink for Tables : Free Unused Space
- ALTER TABLE ... SHRINK SPACE Command : Online Segment Shrink for Tables, LOBs and IOTs
- Reclaiming Unused Space in Datafiles
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 (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>
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.
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>
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:
- ALTER TABLE
- SecureFiles Shrink in Oracle 21c
- Online Segment Shrink for Tables : Free Unused Space
- ALTER TABLE ... SHRINK SPACE Command : Online Segment Shrink for Tables, LOBs and IOTs
- Reclaiming Unused Space in Datafiles
Hope this helps. Regards Tim...