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;

關於收縮表和表空間的相關概念（Shrinking Database Segments Online）

相關文章