压缩段---shrink segment

文档地址：http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_3001.htm#i2192484

 

 

(size_clause::=)

shrink_clause::=

Description of the illustrationshrink_clause.gif

 

shrink_clause

 

    The shrink clause lets you manually shrink space in a table,index-organized table or its overflow segment, index, partition,subpartition, LOB segment, materialized view, or materialized viewlog. This clause is valid only for segments in tablespaces withautomatic segment management. By default, Oracle Databasecompacts the segment, adjusts the high water mark, and releases therecuperated spaceimmediately.--默认的，数据库在压缩段的时候，会调整高水位线，同时立即释放已经挽回的表空间。

 

    Compacting the segment requires row movement. Therefore, you mustenable row movement for the object you want to shrink beforespecifying this clause.Further, if your application hasany rowid-based triggers, you should disable them before issuingthis clause.

 

    COMPACTIf you specifyCOMPACT, then Oracle Database only defragments thesegment space and compacts the table rows for subsequent release.The database does not readjust the high water mark and does notrelease the space immediately. You must issue anotherALTER TABLE ... SHRINKSPACE statement later to complete the operation. Thisclause is useful if you want to accomplish the shrink operation intwo shorter steps rather than one longer step.

 

      For an index or index-organized table, specifyingALTER [INDEX |TABLE] ...SHRINK SPACE COMPACT isequivalent to specifyingALTER [INDEX |TABLE ... COALESCE. Theshrink_clause can be cascaded(please refer to theCASCADE clause, which follows)and compacts the segment more densely than does a coalesceoperation, which can improve performance. However, if you do notwant to release the unused space, then you can use the appropriateCOALESCE clause.

 

 

    CASCADEIf you specifyCASCADE, then Oracle Databaseperforms the same operations on all dependent objects oftable,including secondary indexes on index-organizedtables.--如果指定CASCADE的话，数据库会在表的所有依赖的对象上执行相同的操作(即段的压缩工作，首先压缩段内的记录，即通过一些DML来进行数据重组；然后降低段的HWM水位线），包括索引组织表的二级索引。

 

 

Restrictions onthe shrink_clause Theshrink_clause is subject to thefollowing restrictions:

• You cannot specify this clause for a cluster, a clustered table,or any object with aLONG column.

• Segment shrink is not supported for tables with function-basedindexes or bitmap join indexes.

• This clause does not shrink mapping tables of index-organizedtables, even if you specifyCASCADE.

• You cannot specify this clause for a compressed table.

• You cannot shrink a table that is the master table of anON COMMIT materialized view. Rowidmaterialized views must be rebuilt after the shrink operation.

 

 

补充：

 

从10g开始，oracle开始提供Shrink的命令，假如我们的表空间中支持自动段空间管理(ASSM),就可以使用这个特性缩小段，即降低HWM。

 

segment shrink分为两个阶段：

 

1、数据重组(compact):通过一系列insert、delete操作，将数据尽量排列在段的前面。在这个过程中需要在表上加RX锁，即只在需要移动的行上加锁。由于涉及到rowid的改变，需要enablerowmovement.同时要disable基于rowid的trigger.这一过程对业务影响比较小。(--由于采用COMPACT，只有涉及移动的行才加锁，所以不会锁定整个表，其他的DML操作有部分可以进行，进而减小系统高峰期的性能开销。)

 

2、HWM调整：第二阶段是调整HWM位置，释放空闲数据块。此过程需要在表上加X锁(独享锁，因此整个表都被锁定。如果系统处在高峰期的时候，其他在此表的DML被挂起，会产生严重阻塞。)，会造成表上的所有DML语句阻塞。在业务特别繁忙的系统上可能造成比较大的影响。--参考下面的连接，加了独占锁(X锁)，其他的锁都不能用。(最后一行就是X锁)

 

锁的内容参考： http://docs.oracle.com/cd/B19306_01/server.102/b14220/consist.htm

 

shrink space语句两个阶段都执行。

 

shrink space compact只执行第一个阶段。

 

如果系统业务比较繁忙，可以先执行shrink space compact重组数据,然后在业务不忙的时候再执行shrinkspace降低HWM释放空闲数据块。

 

shrink必须开启行迁移功能。

alter table table_name enable row movement ;

 

注意：alter table XXX enable rowmovement语句会造成引用表XXX的对象(如存储过程、包、视图等)变为无效。执行完成后，最好执行一下utlrp.sql来编译无效的对象。

 

语法：

alter table shrink space [ | compact | cascade ];

alter table shrink space compcat; www.2cto.com

收缩表，相当于把块中数据打结实了，但会保持 high water mark;

alter table shrink space;

 

收缩表，降低 high water mark;

 

alter table shrink space cascade;

 

收缩表，降低 high water mark，并且相关索引也要收缩。

 

alter index idxname shrink space;