Oracle 10g Shrink Table和Shrink Space使用詳解
Oracle 10g Shrink Table的使用是本文我們主要要介紹的內容，我們知道，如果經常在表上執(zhí)行DML操作，會造成數據庫塊中數據分布稀疏，浪費大量空間。同時也會影響全表掃描的性能，因為全表掃描需要訪問更多的數據塊。從Oracle 10g開始，表可以通過shrink來重組數據使數據分布更緊密，同時降低HWM釋放空閑數據塊。
segment shrink分為兩個階段：
1、數據重組(compact):通過一系列insert、delete操作，將數據盡量排列在段的前面。在這個過程中需要在表上加RX鎖，即只在需要移動的行上加鎖。由于涉及到rowid的改變，需要enable row movement.同時要disable基于rowid的trigger.這一過程對業(yè)務影響比較小。
2、HWM調整：第二階段是調整HWM位置，釋放空閑數據塊。此過程需要在表上加X鎖，會造成表上的所有DML語句阻塞。在業(yè)務特別繁忙的系統(tǒng)上可能造成比較大的影響。Shrink Space語句兩個階段都執(zhí)行。Shrink Space compact只執(zhí)行***個階段。
如果系統(tǒng)業(yè)務比較繁忙，可以先執(zhí)行Shrink Space compact重組數據,然后在業(yè)務不忙的時候再執(zhí)行Shrink Space降低HWM釋放空閑數據塊。shrink必須開啟行遷移功能。
alter table table_name enable row movement ;
注意：alter table XXX enable row movement語句會造成引用表XXX的對象(如存儲過程、包、視圖等)變?yōu)闊o效。執(zhí)行完成后，***執(zhí)行一下utlrp.sql來編譯無效的對象。
語法：
- alter table <table_name> shrink space [ <null> | compact | cascade ];
- alter table <table_name> shrink space compcat;
收縮表，相當于把塊中數據打結實了，但會保持high water mark;
alter table <tablespace_name> Shrink Space;
收縮表，降低 high water mark;
alter table <tablespace_name> Shrink Space cascade;
收縮表，降低 high water mark，并且相關索引也要收縮一下下。
alter index idxname Shrink Space;
回縮索引
1：普通表
Sql腳本，改腳本會生成相應的語句
- select'alter table '||table_name||' enable row movement;'||chr(10)||'alter table '||table_name||' shrink space;'||chr(10)from user_tables;
- select'alter index '||index_name||' shrink space;'||chr(10)from user_indexes;
2：分區(qū)表的處理
進行Shrink Space時 發(fā)生ORA-10631錯誤.Shrink Space有一些限制.
在表上建有函數索引（包括全文索引）會失敗。
Sql腳本，改腳本會生成相應的語句
- select 'alter table '||table_name||' enable row movement;'||chr(10)||'alter table '||table_name||' shrink space;'||chr(10) from user_tables where ;
- select 'alter index '||index_name||' shrink space;'||chr(10) from user_indexes where uniqueness='NONUNIQUE' ;
- select 'alter table '||segment_name||' modify subpartition '||partition_name||' shrink space;'||chr(10) from user_segments where segment_type='TABLE SUBPARTITION' ';
Shrink的幾點問題：
1. shrink后index是否需要rebuild：因為shrink的操作也會改變行數據的rowid，那么，如果table上有index時，shrink table后index會不會變?yōu)閁NUSABLE呢？
我們來看這樣的實驗，同樣構建my_objects的測試表：
- create table my_objects tablespace ASSM as select * from all_objects where rownum<20000;
- create index i_my_objects on my_objects (object_id);
- delete from my_objects where object_name like '%C%';
- delete from my_objects where object_name like '%U%';
現在我們來shrink table my_objects：
- SQL> alter table my_objects enable row movement;
- Table altered
- SQL> alter table my_objects shrink space;
- Table altered
- SQL> select index_name,status from user_indexes where index_name='I_MY_OBJECTS';
- INDEX_NAME STATUS
- ------------------------------ --------
- I_MY_OBJECTS VALID
我們發(fā)現，table my_objects上的index的狀態(tài)為VALID，估計shrink在移動行數據時，也一起維護了index上相應行的數據rowid的信息。我們認為，這是對于move操作后需要rebuild index的改進。但是如果一個table上的index數量較多，我們知道，維護index的成本是比較高的，shrink過程中用來維護index的成本也會比較高。
2. shrink時對table的lock
在對table進行shrink時，會對table進行怎樣的鎖定呢？當我們對table MY_OBJECTS進行shrink操作時，查詢v$locked_objects視圖可以發(fā)現，table MY_OBJECTS上加了row-X (SX) 的lock：
- SQL>select OBJECT_ID, SESSION_ID,ORACLE_USERNAME,LOCKED_MODE from v$locked_objects;
- OBJECT_ID SESSION_ID ORACLE_USERNAME LOCKED_MODE
- ---------- ---------- ------------------ -----------
- 55422 153 DLINGER 3
- SQL> select object_id from user_objects where object_name = 'MY_OBJECTS';
- OBJECT_ID
- ----------
- 55422
那么，當table在進行shrink時，我們對table是可以進行DML操作的。
3．shrink對空間的要求
我們在前面討論了shrink的數據的移動機制，既然oracle是從后向前移動行數據，那么，shrink的操作就不會像move一樣，shrink不需要使用額外的空閑空間。
【編輯推薦】
