Shrinking Database Segments Online

标签：space Shrinking Segments segments Online table operation segment shrink

You use online segment shrink to reclaim fragmented free space below the high water mark in an Oracle Database segment.

The benefits of segment shrink are these:

• Compaction of data leads to better cache utilization, which in turn leads to better online transaction processing (OLTP) performance.

• The compacted data requires fewer blocks to be scanned in full table scans, which in turns leads to better decision support system (DSS) performance.

Segment shrink is an online, in-place operation. DML operations and queries can be issued during the data movement phase of segment shrink. Concurrent DML operations are blocked for a short time at the end of the shrink operation, when the space is deallocated. Indexes are maintained during the shrink operation and remain usable after the operation is complete. Segment shrink does not require extra disk space to be allocated.

Segment shrink reclaims unused space both above and below the high water mark. In contrast, space deallocation reclaims unused space only above the high water mark. In shrink operations, by default, the database compacts the segment, adjusts the high water mark, and releases the reclaimed space.

Segment shrink requires that rows be moved to new locations. Therefore, you must first enable row movement in the object you want to shrink and disable any rowid-based triggers defined on the object. You enable row movement in a table with theALTERTABLE...ENABLEROWMOVEMENTcommand.

Shrink operations can be performed only on segments in locally managed tablespaces with automatic segment space management (ASSM). Within an ASSM tablespace, all segment types are eligible for online segment shrink except these:

• IOT mapping tables

• Tables with rowid based materialized views

• Tables with function-based indexes

• SECUREFILELOBs

• Tables compressed with the following compression methods:

  • Basic table compression usingROW STORE COMPRESS BASIC

  • Warehouse compression usingCOLUMN STORE COMPRESS FOR QUERY

  • Archive compression usingCOLUMN STORE COMPRESS FOR ARCHIVE

  However, tables compressed with advanced row compression usingROW STORE COMPRESS ADVANCEDare eligible for online segment shrink. See"Consider Using Table Compression"for information about table compression methods.

Note:

Shrinking database segments online might cause dependent database objects to become invalid. See"About Object Dependencies and Object Invalidation".

See Also:

Oracle Database SQL Language Referencefor more information on theALTERTABLEcommand.

Invoking Online Segment Shrink

Before invoking online segment shrink, view the findings and recommendations of the Segment Advisor. For more information, see"Using the Segment Advisor".

You invoke online segment shrink with Cloud Control or with SQL commands in SQL*Plus. The remainder of this section discusses the command line method.

Note:

You can invoke segment shrink directly from the Recommendation Details page in Cloud Control. Or, to invoke segment shrink for an individual table in Cloud Control, display the table on the Tables page, select the table, and then clickShrink Segmentin the Actions list. (SeeFigure 19-1.) Perform a similar operation in Cloud Control to shrink indexes, materialized views, and so on.

You can shrink space in a table, index-organized table, index, partition, subpartition, materialized view, or materialized view log. You do this usingALTERTABLE,ALTERINDEX,ALTERMATERIALIZEDVIEW, orALTERMATERIALIZEDVIEWLOGstatement with theSHRINK SPACEclause.

Two optional clauses let you control how the shrink operation proceeds:

• TheCOMPACTclause lets you divide the shrink segment operation into two phases. When you specifyCOMPACT, Oracle Database defragments the segment space and compacts the table rows but postpones the resetting of the high water mark and the deallocation of the space until a future time. This option is useful if you have long-running queries that might span the operation and attempt to read from blocks that have been reclaimed. The defragmentation and compaction results are saved to disk, so the data movement does not have to be redone during the second phase. You can reissue theSHRINK SPACEclause without theCOMPACTclause during off-peak hours to complete the second phase.

• TheCASCADEclause extends the segment shrink operation to all dependent segments of the object. For example, if you specifyCASCADEwhen shrinking a table segment, all indexes of the table will also be shrunk. (You need not specifyCASCADEto shrink the partitions of a partitioned table.) To see a list of dependent segments of a given object, you can run theOBJECT_DEPENDENT_SEGMENTSprocedure of theDBMS_SPACEpackage.

As with other DDL operations, segment shrink causes subsequent SQL statements to be reparsed because of invalidation of cursors unless you specify theCOMPACTclause.

Examples

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

ALTER TABLE employees SHRINK SPACE CASCADE;

Shrink aBASICFILELOB segment only:

ALTER TABLE employees MODIFY LOB (perf_review) (SHRINK SPACE);

Shrink a single partition of a partitioned table:

ALTER TABLE customers MODIFY PARTITION cust_P1 SHRINK SPACE;

Shrink an IOT index segment and the overflow segment:

ALTER TABLE cities SHRINK SPACE CASCADE;

Shrink an IOT overflow segment only:

ALTER TABLE cities OVERFLOW SHRINK SPACE;

 

Deallocating Unused Space

When you deallocate unused space, the database frees the unused space at the unused (high water mark) end of the database segment and makes the space available for other segments in the tablespace.

 Before deallocation, you can run theUNUSED_SPACEprocedure of theDBMS_SPACEpackage, which returns information about the position of the high water mark and the amount of unused space in a segment. For segments in locally managed tablespaces with automatic segment space management, use theSPACE_USAGEprocedure for more accurate information on unused space.

See Also:

Oracle Database PL/SQL Packages and Types Referencecontains the description of theDBMS_SPACEpackage

The following statements deallocate unused space in a segment (table, index or cluster):

ALTER TABLE table DEALLOCATE UNUSED KEEP integer;
ALTER INDEX index DEALLOCATE UNUSED KEEP integer;
ALTER CLUSTER cluster DEALLOCATE UNUSED KEEP integer;

TheKEEPclause is optional and lets you specify the amount of space retained in the segment. You can verify that the deallocated space is freed by examining theDBA_FREE_SPACEview.

 

 

 

 

 

 

 

 

 

标签：space,Shrinking,Segments,segments,Online,table,operation,segment,shrink
From： https://www.cnblogs.com/wonchaofan/p/16721188.html