Online Segment Shrink)

Oracle Online Segment shrinkage (Online Segment Shrink) refers to the fragments in the Online Segment space. It has the following features:

• Online, that is, in the segment compression process, DML is almost unaffected (DML will be blocked only a short period before the end)
• In-place operations, no extra space required
• After the contraction, the index is still available.

It can not only reclaim the unused space above the high water level line, but also reclaim the space used for the high water level. Its main steps are as follows:

1. Compressed segment Space
2. Adjust high water level line
3. Release the reclaimed Space

There are two prerequisites for using Shrink:

1. The row movement must be enabled for the table.
2. The segment space management must be auto

The syntax for CIDR Block contraction is as follows:

Alter table/index/materialized view object_name shrink space [cascade] [compact];

• Cascade: compresses all dependent objects. For example, if a table statement is compressed with cascade, all indexes on the table are compressed.
• Compact: the compression process is divided into two phases: the statement in the first phase includes compact, which compresses the segment space. In this process, the RX lock must be applied to the table, that is, only lock the row to be moved. Due to changes in rowid, enable row movement is required. At the same time, the rowid-based trigger must be disable. This process has little impact on the business .; In the second stage, the statement does not contain compact, so the high water level is adjusted and the space for recovery is released. This process requires an X lock on the table, which will block all DML statements on the table. It may have a big impact on systems with extremely busy services. For large tables, the compact option is recommended.

The following example shows how to create a test table and an index on it:

SYS @ TEST16> create table t as select * from dba_objects;

Table created.

SYS @ TEST16> create index I on t (object_id );

Index created.

View the space usage of tables and indexes:

SYS @ TEST16> select segment_name, segment_type, bytes, blocks from dba_segments where owner = 'test' and segment_name in
2 (select 't'from dual union all select index_name from dba_indexes where table_owner = 'test' and TABLE_NAME = 'T ');

SEGMENT_NAME SEGMENT_TYPE BYTES BLOCKS
--------------------------------------------------------------------
Tb table 9437184 1152
I INDEX 2097152 256

After deleting table data, the table and index space are not recovered:

SYS @ TEST16> delete from t;

72568 rows deleted.

SYS @ TEST16> commit;

Commit complete.

SYS @ TEST16> select segment_name, segment_type, bytes, blocks from dba_segments where owner = 'test' and segment_name in
2 (select 't'from dual union all select index_name from dba_indexes where table_owner = 'test' and TABLE_NAME = 'T ');

SEGMENT_NAME SEGMENT_TYPE BYTES BLOCKS
--------------------------------------------------------------------
Tb table 9437184 1152
I INDEX 2097152 256

The space is not actually reclaimed until the segment is reduced:

SYS @ TEST16> alter table t enable row movement;

Table altered.

SYS @ TEST16> alter table t shrink space cascade compact;

Table altered.

SYS @ TEST16> alter table t shrink space cascade;

Table altered.

SYS @ TEST16> select segment_name, segment_type, bytes, blocks from dba_segments where owner = 'test' and segment_name in
2 (select 't'from dual union all select index_name from dba_indexes where table_owner = 'test' and TABLE_NAME = 'T ');

SEGMENT_NAME SEGMENT_TYPE BYTES BLOCKS
--------------------------------------------------------------------
T table 65536 8
I INDEX 65536 8

Recommended reading:

Basic Oracle tutorial-copying a database through RMAN

Reference for RMAN backup policy formulation

RMAN backup learning notes

Oracle Database Backup encryption RMAN Encryption