Shrink database segment online in Oracle 11g

1. Online segment shrink recycles idle space that uses fragments below the high water level line in the Oracle database segment. Segment shrink has the following benefits:

1. compressed data will lead to better cache utilization, resulting in better online transaction processing (OLTP) performance.

2. During full table scan, fewer blocks are required for shrinking data, which in turn leads to better performance of the Decision Support System (DSS ).

2. segment shrink is an online, local operation. DML operations and queries can be performed during the data movement phase of CIDR blocks. When the space is recycled, concurrent DML operations are blocked for a short period of time at the end of the contraction operation. Indexes are maintained during the contraction operation and can be used after the operation is completed. You do not need to allocate additional disk space for CIDR blocks.

3. segment shrink recovers the idle space on and off the high water level line. In contrast, deallocating unusedspace only removes the space not used on the high water level line. In the shrink operation, by default, the database compresses the segment, adjusts the high level mark, and releases the recycle space.

4. segment contraction requires that the row be transferred to a new location. Therefore, you must first make the object you want to contract allow row movement and disable any trigger object defined based on rowid. UseALTERTABLE...ENABLEROWMOVEMENTCommand to enable line movement.

5. The contraction operation can only be performed on segments in the tablespace locally managed by the automatic segment space management (assm) function. In an assm tablespace, all segment types are eligible for online segment shrinkage except for the following:

· Iot mapping tables

· Tables with rowid based materializedviews

· Tables with function-based indexes

· Securefile lobs

· Compressed tables

 

Shrinking the database segment online may result in invalid dependent database objects.

6. The shrink space objects include: Table, index-organized table, index, partition, subpartition, and materialized.
View, ormaterialized view log. Use the followingSHRINK SPACEStatement:ALTERTABLE,ALTERINDEX,ALTERMATERIALIZEDVIEW,
OrALTERMATERIALIZEDVIEWLOG 

7. Two optional terms allow you to control shrink operations:

 COMPACTClause
The shrink segment operation is divided into two stages .. WhenCOMPACT,Oracle databases sort disk fragments and compress the rows in the table for the segment space, but delay resetting the high water level line and recycling the space until a certain time in the future. This option is useful if you have a long-running query that may span operations and try to read the block that has been withdrawn. The results of fragment and compression are saved to the disk. Therefore, data migration does not need to be redone in the second stage and can be used during off-peak hours.SHRINKSPACEDo not addCOMPACT，Complete the second stage.

 

CASCADEExtend the segment shrink operation to objects in all dependent segments. For example, if you specifyCASCADEAll indexes of this table will be reduced. (You do not need to specify cascade to narrow down the Partition Table of the partition ).

Unlike other DDL operations, segment shrink causes subsequent SQL statements to be reparsed due to invalid cursors, unless you specify a Compact Clause

8. Examples

Shrink a table and all of its dependent segments (includingbasicfile lob segments ):

Alter tableemployees shrink space cascade;

Shrink abasicfile lob segment only:

Alter tableemployees modify lob (perf_review) (shrink space );

Shrink a singlepartition of a partitioned table:

Alter tablecustomers modify partition cust_p1 shrink space;

Shrink an Iot indexsegment and the overflow segment:

Alter table citiesshrink space cascade;

Shrink an iotoverflow segment only:

Alter table citiesoverflow shrink space;