oracle shrink
一、shrink segment兩個好處
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:
l Compaction of data leads to better cache utilization, which in turn leads to better online transaction processing (OLTP) performance.
l The compacted data requires fewer blocks to be scanned in full table scans, which in turns leads to better decision support system (DSS) performance.
二、shrink操作兩個階段
segment shrink分為兩個階段:
1、 資料重組(compact):通過一系列insert、delete操作,將資料儘量排列在段的前面。在這個過程中需要在表上加Row Exclusive(RX)鎖,即只在需要移動的行上加鎖。由於涉及到rowid的改變,需要enable row movement。同時要disable基於rowid的trigger。這一過程對業務影響較小。
2、 HWM調整:第二階段是調整HWM位置,釋放空閒資料塊。此過程需要在表上加X鎖,會造成表上的所有DML語句阻塞。在業務特別繁忙的系統可能造成比較大的影響。
shrink space語句兩個階段都執行;
shrink space compact只執行第一個階段。
如果系統業務比較繁忙,可以先執行shrink space compact重組資料,然後在業務不忙得時候再執行shrink space降低HWM釋放空閒資料塊。
三、shrink的操作前提條件
1、Shrink operations can be performed only on segments in locally managed tablespaces with automatic segment space management (ASSM).
2、 shrink必須開啟行遷移功能!
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.
alter table table_name enable row movement;
注意:此操作會造成引用表的物件(如儲存過程、包、檢視等)變為無效。執行完shrink後,最好執行一下utlrp.sql來編譯無效的物件。
四、shrink語法:
alter table
compact:重組表,只執行第一階段,相當於把塊中資料打結實了,但會保持high water mark。
cascade:收縮表,降低high water mark,並且相關索引也要收縮一下。
五、哪些segment可以被shrink
Within an ASSM tablespace, all segment types are eligible for online segment shrink except these:
l IOT mapping tables
l Tables with rowid based materialized views
l Tables with function-based indexes
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/25744374/viewspace-731336/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- oracle shrink tableOracle
- Oracle 10g Shrink Table - Shrink Space 收縮空間Oracle 10g
- How to Shrink Undo Segment In Oracle DatabaseOracleDatabase
- Oracle中shrink space命令詳解Oracle
- 轉載-oracle Shrink命令的使用Oracle
- [Oracle] Shrink space & Table move比較Oracle
- Oracle 10g Shrink Table 詳解Oracle 10g
- oracle 分割槽表進行shrink操作Oracle
- oracle10g shrink space 降低HWMOracle
- 【轉】Oracle:MOVE與SHRINK命令相比較Oracle
- Oracle move和shrink釋放高水位空間Oracle
- Oracle 11g alter table move與shrink spaceOracle
- oracle 10g__alter table shrink space compactOracle 10g
- 【SHRINK】Oracle收縮表的詳細命令參考Oracle
- Oracle OCP IZ0-053 Q432(Segment Shrink)Oracle
- Oracle IZ0-053 Q277(Table shrink space)Oracle
- Oracle中shrink space命令詳解[轉]--還示測試Oracle
- assm下oracle為什麼為segment提供了shrink功能SSMOracle
- vector::shrink_to_fit()
- CSS flex-shrinkCSSFlex
- vector shrink_to_fit
- How to adjust the high watermark in ORACLE 10g – ALTER TABLE SHRINKOracle 10g
- Oracle資料庫管理——表資料庫高水位及shrink操作Oracle資料庫
- Oracle OCP 1Z0 053 Q96(Shrink Space Compact)Oracle
- Shrink操作的注意事項
- shrink space的最佳實踐
- 7 、shrink table and its dependent segments
- shrink 操作對索引的影響索引
- SQL Server 2000 shrink tempdbSQLServer
- table move 與 shrink 的區別
- ALTER TABLE MOVE | SHRINK SPACE區別
- shrink收縮檔案空間
- C++ shrink_to_fit()的實現C++
- DATAFILE SHRINK 釋放系統空間
- sql server日誌不能shrink或truncateSQLServer
- ALTER TABLE MOVE和SHRINK SPACE區別
- oracle10g_alter table shrink space_compact_cascade回收空間測試(一)Oracle
- SEGMENT SHRINK and Details. (文件 ID 242090.1)AI