[Oracle] Online Segment contraction (Online Segment Shrink)

[Oracle] Online Segment Shrink refers to the fragments in the Online Segment space. It has the following features: Online, that is, during the segment compression process, DML is almost unaffected (DML will be blocked only a short period before the end) in-place operations, after no additional space shrinkage is required, 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: there are two prerequisites for using Shrink to adjust the size of the compressed segment space to release the reclaimed space: the table must enable segment space management (segment space management) for the tablespace where the row movement table segment is located) the syntax for auto online segment shrinkage must be as follows:

[sql] 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 into compact: the compression process is divided into two phases: in the first phase, the statement contains compact, which compresses the segment space. In this process, the RX lock must be applied to the table, that is, the row to be moved must be locked. 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:

[sql] 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:

[sql] 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                 9437184       1152  I                              INDEX                 2097152        256  

 

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

[sql] 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  ------------------------------ ------------------ ---------- ----------  T                              TABLE                 9437184       1152  I                              INDEX                 2097152        256  

 

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

[sql] 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