Online segment contraction of Oracle (on-line Segment Shrink)

Oracle's online segment contraction (on-line Segment Shrink) refers to the online defragmentation of fragments in the space, which has the following characteristics:

Online, that is, DML is almost unaffected during segment compression (only a short time before the end, DML will be blocked)

In-place operation, no extra space required

Index remains available after shrink completes

It can not only recover the unused space above the high watermark, but also recover the space used under the high watermark, and its main steps are:

Compress segment Space

Adjust high watermark

Free up space for recovery

There are two prerequisites for using shrink:

Table must enable row movement

Segment space management for table spaces with table segments (segment spaces management) must be auto

The syntax for shrinking the segment is as follows:

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

Cascade: Refers to compressing all dependent objects, such as compressed table statements plus cascade, all indexes on the table are compressed

Compact: The compression process is divided into two stages: the first stage of the statement with the compact, compressed segment space, in this process need to add RX lock on the table, that is, only in the need to move the lock on the line. Because it involves rowid changes, you need to enable row movement. At the same time to disable based on ROWID trigger. This process has a relatively small impact on the business. The second stage statement does not carry the compact, adjusts the high water level and releases the recovered space. This procedure requires an X lock on the table, which causes all DML statements on the table to block. may have a larger impact on a system that is particularly busy. For large tables, it is recommended that the compact option be adopted.

Let's take a practical example to illustrate the following:
First, 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.

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