关于收缩表和表空间的相关概念（Shrinking Database Segments Online）

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.

此段介绍了关于收缩段空间的好处，即对OLTP和DSS系统都能提升一个更好的性能。


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.

段收缩是一个线上的本地化的操作。dml的操作和查询操作在段收缩的数据逐步移动的过程中不收影响。并发的DML操作会在收缩操作的最后，即空间重新分配的时候，被短暂的阻塞，索引在收缩的过程中会被维护并且在操作完成之后仍然是可用的。段收缩不需要额外的磁盘空间。

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 the ALTER  TABLE  ... ENABLE  ROW  MOVEMENT  command.

段收缩需要行被重新定位，因此在进行段收缩前必须首先开启行迁移并且使所有基于这些对象rowid的触发器失效。

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

• SECUREFILE LOBs

• Compressed tables

收缩操作只能在本地开启自动段空间管理的表空间执行，对于自动段空间管理的表空间，所有的段类型对于线上段收缩都是合法的除了一下这些：

索引组织映射表
基于rowid的物化视图表
基于函数索引的表
安全文件的LOB
压缩表

You can shrink space in a table, index-organized table, index, partition, subpartition, materialized view, or materialized view log. You do this using ALTER  TABLE ,ALTER  INDEX , ALTER  MATERIALIZED  VIEW , or ALTER  MATERIALIZED  VIEW  LOG  statement with the SHRINK SPACE  clause.

你可以收缩表的空间，索引组织表的空间，索引，分区，子分区，物化视图或者物化视图的日志。你可以做这些通过
alter table
alter index
alter materialized view
alter materialized view log    ... +shrink space.

Two optional clauses let you control how the shrink operation proceeds:
两个选项可以让你控制如何进行shrink操作

• The COMPACT clause lets you divide the shrink segment operation into two phases. When you specify COMPACT, 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 the SHRINK SPACE clause without the COMPACT clause during off-peak hours to complete the second phase.

COMPACT选项可以将收缩段空间分成两个阶段，如果你使用COMPACT，oracle数据库会收集段空间的碎片并压缩表的行但是会延期重新设定高水位线和空间的回收到未来的一个时间点。这个选项对于那些会跨过这个操作并尝试从已经被回收的块中读取信息的长时间运行的查询的服务器是有用的。这个收集碎片并压缩的结果会被保存在磁盘上，因此数据的迁移不会再被重做在第二个阶段，你可以在业务低峰期重新发起shrink space的选项不需要再加上compact选项，从而完成第二阶段的工作。

• The CASCADE clause extends the segment shrink operation to all dependent segments of the object. For example, if you specify CASCADE when shrinking a table segment, all indexes of the table will also be shrunk. (You need not specify CASCADE to shrink the partitions of a partitioned table.) To see a list of dependent segments of a given object, you can run the OBJECT_DEPENDENT_SEGMENTS procedure of the DBMS_SPACE package.

cascade选项会扩展段收缩操作到所有相关的对象段，例如，如果你指定了cascade选项当收缩表的段时，所有的基于这些表的索引也都会被收缩掉。（你不需要指定cascade选项去收缩分区表的分区）。如果你需要看这些表的依赖对象，可以运行包DBMS_SPACE下的OBJECT_DEPENDENT_SEGMENTS存储过程。

As with other DDL operations, segment shrink causes subsequent SQL statements to be reparsed because of invalidation of cursors unless you specify theCOMPACT  clause.
和其他DDL操作一样，段收缩会引起之后的SQL的重新解析，因为游标的失效，除非指定COMPACT选项。

Examples

Shrink a table and all of its dependent segments (including BASICFILE LOB segments):

ALTER TABLE employees SHRINK SPACE CASCADE;

Shrink a BASICFILE LOB 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;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29802484/viewspace-1869232/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29802484/viewspace-1869232/