關於Oracle數據庫中行遷移/行鏈接的問題

　　一行遷移/行鏈接的介紹

　　在實際的工作中我們經常會碰到一些Oracle數據庫性能較低的問題當然引起Oracle數據庫性能較低的原因是多方面的我們能夠通過一些正確的設計和診斷來盡量的避免一些Oracle數據庫性能不好Row Migration (行遷移) & Row Chaining (行鏈接)就是其中我們可以盡量避免的引起Oracle數據庫性能低下的潛在問題通過合理的診斷行遷移/行鏈接我們可以較大幅度上提高Oracle數據庫的性能

　　那究竟什麼是行遷移/行鏈接呢先讓我們從Oracle的block開始談起

　　操作系統的最小讀寫操作單元是操作系統的block所以當創建一個Oracle數據庫的時候我們應該講數據庫的block size設置成為操作系統的block size的整數倍Oracle block是Oracle數據庫中讀寫操作的最小單元Oraclei之前的Oracle數據庫版本中Oracle block一旦在創建數據庫的時候被設定後就沒法再更改為了在創建數據庫之前確定一個合理的Oracle block的大小我們需要考慮一些因素例如數據庫本身的大小以及並發事務的數量等使用一個合適的Oracle block大小對於數據庫的調優是非常重要的

　　一個Oracle block由三個部分組成分別是數據塊頭自由空間實際數據三部份組成

　　數據塊頭主要包含有數據塊地址的一些基本信息和段的類型以及表和包含有數據的實際行的地址

　　自由空間是指可以為以後的更新和插入操作分配的空間大小由PCTFREE和PCTUSED兩個參數影響

　　實際數據是指在行內存儲的實際數據

　　當創建或者更改任何表和索引的時候Oracle在空間控制方面使用兩個存儲參數

　　PCTFREE:為將來更新已經存在的數據預留空間的百分比

　　PCTUSED:用於為插入一新行數據的最小空間的百分比這個值決定了塊的可用狀態可用的塊時可以執行插入的塊不可用狀態的塊只能執行刪除和修改可用狀態的塊被放在freelist中

　　當表中一行的數據不能在一個數據block中放入的時候這個時候就會發生兩種情況一種是行鏈接另外一種就是行遷移了

　　行鏈接產生在第一次插入數據的時候如果一個block不能存放一行記錄的情況下這種情況下Oracle將使用鏈接一個或者多個在這個段中保留的block存儲這一行記錄行鏈接比較容易發生在比較大的行上例如行上有LONGLONG RAWLOB等數據類型的字段這種時候行鏈接是不可避免的會產生的

　　當一行記錄初始插入的時候事可以存儲在一個block中的由於更新操作導致行長增加了而block的自由空間已經完全滿了這個時候就產生了行遷移在這種情況下Oracle將會遷移整行數據到一個新的block中（假設一個block中可以存儲下整行數據）Oracle會保留被遷移行的原始指針指向新的存放行數據的block這就意味著被遷移行的ROW ID是不會改變的

　　當發生了行遷移或者行鏈接對這行數據操作的性能就會降低因為Oracle必須要掃描更多的block來獲得這行的信息

　　下面舉例來具體說明行遷移/行鏈接的產生過程

　　先創建一個pctfree為和pctused為的測試表

　　create table test（

　　col char()

　　col number)

　　storage (

　　pctfree

　　pctused );

　　當插入一條記錄的時候Oracle會在free list中先去尋找一個自由的塊並且將數據插入到這個自由塊中而在free list中存在的自由的塊是由pctfree值決定的初始的空塊都是在free list中的直到塊中的自由空間達到pctfree的值此塊就會從free list中移走而當此塊中的使用空間低於pctused的時候此塊又被重新放到free list中

　　Oracle使用free list機制可以大大的提高性能對於每次的插入操作Oracle只需要查找free list就可以了而不是去查找所有的block來尋找自由空間

　　具體我們通過下面的一個試驗來查看行鏈接和行遷移是如何產生並在數據文件中體現出來的

　　先查看ALLAN這個表空間的數據文件號為了便於測試我只建立了一個數據文件

　　SQL> select file_id from dba_data_files where tablespace_name=ALLAN;

　　FILE_ID

　　

　　

　　創建一個測試表test

　　SQL> create table test ( x int primary key a char() b char() c char() d char() e char() ) tablespace allan;

　　Table created

　　因為我的數據庫的db_block_size是K所以我創建的表有五個字段每個占個字節這樣一行記錄大約K就能超過一個block的大小了

　　然後插入一行記錄只有一個字段的

　　SQL> insert into test(x) values ();

　　 row created

　　SQL> commit;

　　Commit complete

　　查找這行記錄所在的block並dump出來

　　SQL> select dbms_rowidrowid_block_number(rowid) from test;

　　DBMS_ROWIDROWID_BLOCK_NUMBER(ROWID)

　　

　　

　　SQL> alter system dump datafile block ;

　　System altered

　　在udump目錄下查看trace文件的內容如下

　　Start dump data blocks tsn: file#: minblk maxblk

　　buffer tsn: rdba: xc (/)

　　scn: xf seq: x flg: x tail: xf

　　frmt: x chkval: x type: x=trans data

　　Block header dump: xc

　　Object id on Block? Y

　　seg/obj: xccd csc: xef itc: flg: O typ: DATA

　　fsl: fnx: x ver: x

　　Itl Xid Uba Flag Lck Scn/Fsc

　　x xaead xde U fsc xf

　　x x x fsc x

　　data_block_dumpdata header at xadbc

　　===============

　　tsiz: xfa

　　hsiz: x

　　pbl: xadbc

　　bdba: xc

　　

　　flag=

　　ntab=

　　nrow=

　　frre=

　　fsbo=x

　　fseo=xfa

　　avsp=xf

　　tosp=xf

　　xe:pti[] nrow= offs=

　　x:pri[] offs=xfa

　　block_row_dump:

　　tab row @xfa

　　tl: fb: HFL lb: x cc:

　　col : [ ] c

　　end_of_block_dump

　　End dump data blocks tsn: file#: minblk maxblk

　　對其中的一些信息做一些解釋

　　Fb:H是指行記錄的頭L是指行記錄的最後一列F是指行記錄的第一列

　　Cc列的數量

　　Nrid對於行鏈接或者行遷移來說的下一個row id的值

　　由上面的dump信息我們可以看出來當前表test是沒有行鏈接或者行遷移的

　　然後更新test表並重新dump出來

　　SQL> update test set a=testb=testc=testd=teste=test where x=;

　　 row updated

　　SQL> commit;

　　Commit complete

　　此時應該有行遷移/行鏈接產生了

　　SQL> alter system dump datafile block ;

　　System altered

　　在udump目錄下查看trace文件的內容如下

　　Start dump data blocks tsn: file#: minblk maxblk

　　buffer tsn: rdba: xc (/)

　　scn: xb seq: x flg: x tail: xb

　　frmt: x chkval: x type: x=trans data

　　Block header dump: xc

　　Object id on Block? Y

　　seg/obj: xccd csc: x itc: flg: typ: DATA

　　fsl: fnx: x ver: x

　　Itl Xid Uba Flag Lck Scn/Fsc

　　x xaead xde C scn xf

　　x xae xb U fsc xb

　　data_block_dumpdata header at xadbc

　　===============

　　tsiz: xfa

　　hsiz: x

　　pbl: xadbc

　　bdba: xc

　　

　　flag=

　　ntab=

　　nrow=

　　frre=

　　fsbo=x

　　fseo=xa

　　avsp=xc

　　tosp=xc

　　xe:pti[] nrow= offs=

　　x:pri[] offs=xa

　　block_row_dump:

　　tab row @xa

　　tl: fb: HFN lb: x cc:

　　nrid: xc

　　col : [ ] c

　　col : []

　　

　　

　　…………

　　col : []

　　

　　

　　end_of_block_dump

　　End dump data blocks tsn: file#: minblk maxblk

　　我們不難看出nrid出現了值指向了下一個row id證明剛剛的update操作使這行記錄產生了行鏈接或者行遷移了

　　二行遷移/行鏈接的檢測

　　通過前面的介紹我們知道行鏈接主要是由於數據庫的db_block_size不夠大對於一些大的字段沒法在一個block中存儲下從而產生了行鏈接對於行鏈接我們除了增大db_block_size之外沒有別的任何辦法去避免但是因為數據庫建立後db_block_size是不可改變的（在i之前）對於Oraclei的數據庫我們可以對不同的表空間指定不同的db_block_size因此行鏈接的產生幾乎是不可避免的也沒有太多可以調整的地方行遷移則主要是由於更新表的時候由於表的pctfree參數設置太小導致block中沒有足夠的空間去容納更新後的記錄從而產生了行遷移對於行遷移來說就非常有調整的必要了因為這個是可以調整和控制清除的

　　如何檢測數據庫中存在有了行遷移和行鏈接呢？我們可以利用Oracle數據庫自身提供的腳本utlchainsql（在$ORACLE_HOME/rdbms/admin目錄下）生成chained_rows表然後利用ANALYZE TABLE table_name LIST CHAINED ROWS INTO chained_rows命令逐個分析表將分析的結果存入chained_rows表中從utlchainsql腳本中我們看到chained_rows的建表腳本對於分區表cluster表都是適用的然後可以使用拼湊語句的辦法生成分析所需要的表的腳本並執行腳本將具體的分析數據放入Chained_rows表中例如下面是分析一個用戶下所有表的腳本

　　SPOOL list_migation_rowssql

　　SET ECHO OFF

　　SET HEADING OFF

　　SELECT ANALYZE TABLE || table_name || LIST CHAINED ROWS INTO chained_rows; FROM user_tables;

　　SPOOL OFF

　　然後查詢chained_rows表可以具體查看某張表上有多少的行鏈接和行遷移

　　SELECT table_name count(*) from chained_rows GROUP BY table_name;

　　當然也可以查詢v$sysstat視圖中的table fetch continued row列得到當前的行鏈接和行遷移數量

　　SELECT name value FROM v$sysstat WHERE name = table fetch continued row;

　　可以使用如下的腳本來直接查找存在有行鏈接和行遷移的表自動完成所有的分析和統計

　　accept owner prompt Enter the schema name to check for Row Chaining (RETURN for All):
        prompt
        prompt
        accept table prompt Enter the table name to check (RETURN for All tables owned by &owner):
        prompt
        prompt
        set head off serverout on term on feed off veri off echo off
        !clear
        prompt
        declare
        v_owner varchar();
        v_table varchar();
        v_chains number;
        v_rows number;
        v_count number := ;
        sql_stmt varchar();
        dynamicCursor INTEGER;
        dummy INTEGER;
        cursor chains is
        select count(*) from chained_rows;
        cursor analyze is
        select owner table_name
        from sysdba_tables
        where owner like upper(%&owner%)
        and table_name like upper(%&table%)
        order by table_name;
        begin
        dbms_outputenable();
        open analyze;
        fetch analyze into v_owner v_table;
        while analyze%FOUND loop
        dynamicCursor := dbms_sqlopen_cursor;
        sql_stmt := analyze table ||v_owner||||v_table|| list chained rows into chained_rows;
        dbms_sqlparse(dynamicCursor sql_stmt dbms_sqlnative);
        dummy := dbms_sqlexecute(dynamicCursor);
        dbms_sqlclose_cursor(dynamicCursor);
        open chains;
        fetch chains into v_chains;
        if (v_chains != ) then
        if (v_count = ) then
        dbms_outputput_line(CHR()||CHR()||CHR()||<<<<< Chained Rows Found >>>>>);
        v_count := ;
        end if;
        dynamicCursor := dbms_sqlopen_cursor;
        sql_stmt := Select count(*) v_rows|| From ||v_owner||||v_table;
        dbms_sqlparse(dynamicCursor sql_stmt dbms_sqlnative);
        dbms_sqlDEFINE_COLUMN(dynamicCursor v_rows);
        dummy := dbms_sqlexecute(dynamicCursor);
        dummy := dbms_sqlfetch_rows(dynamicCursor);
        dbms_sqlCOLUMN_VALUE(dynamicCursor v_rows);
        dbms_sqlclose_cursor(dynamicCursor);
        dbms_outputput_line(v_owner||||v_table);
        dbms_outputput_line(CHR()||> Has ||v_chains|| Chained Rows and ||v_rows|| Num_Rows in it!);
        dynamicCursor := dbms_sqlopen_cursor;
        sql_stmt := truncate table chained_rows;
        dbms_sqlparse(dynamicCursor sql_stmt dbms_sqlnative);
        dummy := dbms_sqlexecute(dynamicCursor);
        dbms_sqlclose_cursor(dynamicCursor);
        v_chains := ;
        end if;
        close chains;
        fetch analyze into v_owner v_table;
        end loop;
        if (v_count = ) then
        dbms_outputput_line(No Chained Rows found in the ||v_owner|| owned Tables!);
        end if;
        close analyze;
        end;
        /
        set feed on head on
        prompt

　　三行遷移和行鏈接的清除

　　由於對於行鏈接來說只能增大db_block_size來清除而db_block_size在創建了數據庫後又是不能改變了的所以這裡對行鏈接的清除不做過多的敘述了主要是針對行遷移來談談在實際的生產系統中如何去清除

　　對於行遷移的清除一般來說分為兩個步驟第一步控制住行遷移的增長使其不在增多第二步清除掉以前存在的行遷移

　　眾所周知行遷移產生的主要原因是因為表上的pctfree參數設置過小導致的而要實現第一步控制住行遷移的增長就必須設置好一個正確合適的pctfree參數否則即使清除了當前的行遷移後馬上又會產生很多新的行遷移當然這個參數也不是越大越好的如果pctfree設置的過大會導致數據塊的利用率低造成空間的大量浪費因此必須設置一個合理的pctfree參數如何去確定一個表上合理的pctfree參數呢一般來說有兩種方法

　　第一種是定量的的設定方法就是利用公式來設定pctfree的大小先使用ANALYZE TABLE table_name ESTIMATE STATISTICS命令來分析要修改pctfree的表然後查看user_tables中的AVG_ROW_LEN列值得到一個平均行長AVG_ROW_LEN然後大量的對表操作之後再次使用上述命令分析表得到第二個平均行長AVG_ROW_LEN然後運用公式 * （AVG_ROW_LENAVG_ROW_LEN）/（AVG_ROW_LENAVG_ROW_LEN + 原始的AVG_ROW_LEN）得出的結果就是定量計算出來的一個合適的pctfree的值這種方法因為是定量計算出來的可能不一定會很准確而且因為要分析表所以對於使用RBO執行計劃的系統不是很適用例如avg_row_len_ = avg_row_len_ = 則平均修改量為 PCTFREE 應調整為 * /（ + ）= %

　　第二種是差分微調的方法先查詢到當前表的pctfree的值然後監控和調整pctfree參數每次增加一點pctfree的大小每次增加的比例不要超過個百分點然後使用ANALYZE TABLE TABLE_NAME LIST CHAINED ROWS INTO chained_rows命令分析每次所有的行遷移和行鏈接的增長情況對於不同的表采取不同的增長比例對於行遷移增長的比較快的表pctfree值就增加的多點對於增長慢的表就增加的少點直到表的行遷移基本保持不增長了為止但是注意不要把pctfree調的過大一般在%以下就可以了否則會造成空間的很大浪費和增加數據庫訪問的IO

　　使用上述的方法控制住了當前表的行遷移的增長之後就可以開始清除之前表上存在的行遷移了是否清除掉行遷移關系到系統的性能是否能夠有很大的提高因此對於以前存在的行遷移是一定而且必須要清除掉的清除掉已經存在的行遷移有很多方法但是並不是所有的方法都能適用所有的情況例如表中的記錄數多少表上的關聯多少表上行遷移的數量多少等等這些因素都會是成為制約你使用什麼方法清除的條件因此根據表的特點和具體情況的不同我們應該采用不同的方法去清除行遷移下面我將逐一介紹各種清除行遷移的方法以及它們各自適用的不同情況

　　方法一傳統的清除行遷移的方法

　　具體步驟如下

　　． 執行$ORACLE_HOME/rdbms/admin目錄下的utlchainsql腳本創建chained_rows表

　　@$ORACLE_HOME/rdbms/admin/utlchainsql

　　． 將存在有行遷移的表（用table_name代替）中的產生行遷移的行的rowid放入到chained_rows表中

　　ANALYZE TABLE table_name LIST CHAINED ROWS INTO chained_rows;

　　． 將表中的行遷移的row id放入臨時表中保存

　　CREATE TABLE table_name_temp AS

　　SELECT * FROM table_name

　　WHERE rowid IN

　　(SELECT head_rowid FROM chained_rows

　　WHERE table_name = table_name);

　　． 刪除原來表中存在的行遷移的記錄行

　　DELETE table_name

　　WHERE rowid IN

　　(SELECT head_rowid

　　FROM chained_rows

　　WHERE table_name = table_name);

　　． 從臨時表中取出並重新插入那些被刪除了的數據到原來的表中並刪除臨時表

　　INSERT INTO table_name SELECT * FROM table_name_temp;

　　DROP TABLE table_name_temp;

　　對於這種傳統的清除RM的方法優點是執行起來過程比較簡單容易實現但是這種算法的缺陷是沒有考慮到表關聯的情況在大多數數據庫中很多表都是和別的表之間有表關聯的有外鍵的限制這樣就造成在步驟中根本無法delete掉存在有行遷移的記錄行所以這種方法能夠適用的表的范圍是有限的只能適用於表上無任何外鍵關聯的表由於這種方法在插入和刪除數據的時候都沒有disable掉索引這樣導致主要消耗時間是在刪除和插入時維持索引樹的均衡上了這個對於如果記錄數不多的情況時間上還比較短但是如果對於記錄數很多的表這個所消耗的時間就不是能夠接受的了顯然這種方法在處理大數據量的表的時候顯然是不可取的

　　以下是一個具體在生產數據庫上清除行遷移的例子在這之前已經調整過表的pctfree參數至一個合適的值了

　　SQL>@$ORACLE_HOME/rdbms/admin/utlchainsql

　　Table created

　　SQL> ANALYZE TABLE CUSTOMER LIST CHAINED ROWS INTO chained_rows;

　　Table analyzed

　　SQL>SELECT count(*) from chained_rows

　　TABLE_NAME COUNT(*)

　　

　　CUSTOMER

　　 rows selected

　　查看在CUSTOMER表上存在的限制:

　　SQL>select CONSTRAINT_NAMECONSTRAINT_TYPETABLE_NAME from USER_CONSTRAINTS where TABLE_NAME=CUSTOMER;

　　CONSTRAINT_NAME C TABLE_NAME

　　

　　PK_CUSTOMER P CUSTOMER

　　SQL>select CONSTRAINT_NAMECONSTRAINT_TYPETABLE_NAME from USER_CONSTRAINTS where R_CONSTRAINT_NAME=PK_CUSTOMER;

　　no rows selected

　　SQL> CREATE TABLE CUSTOMER_temp AS

　　SELECT * FROM CUSTOMER WHERE rowid IN

　　(SELECT head_rowid FROM chained_rows

　　WHERE table_name = CUSTOMER);

　　Table created

　　SQL>select count(*) from CUSTOMER;

　　COUNT(*)

　　

　　

　　SQL> DELETE CUSTOMER WHERE rowid IN

　　(SELECT head_rowid

　　FROM chained_rows

　　WHERE table_name = CUSTOMER);

　　 rows deleted

　　SQL> INSERT INTO CUSTOMER SELECT * FROM CUSTOMER_temp;

　　 rows created

　　SQL> DROP TABLE CUSTOMER_temp;

　　Table dropped

　　SQL> commit;

　　Commit complete

　　SQL> select count(*) from CUSTOMER;

　　COUNT(*)

　　

　　

　　SQL> truncate table chained_rows;

　　Table truncated

　　SQL> ANALYZE TABLE CUSTOMER LIST CHAINED ROWS INTO chained_rows;

　　Table analyzed

　　SQL> select count(*) from chained_rows;

　　COUNT(*)

　　

　　

　　以上整個清除兩萬多行的行遷移過程在三分鐘左右而且全部都在聯機的狀態下完成基本上不會對業務有什麼影響唯一就是在要清除行遷移的表上不能有對外鍵的限制否則就不能采用這個方法去清除了

　　方法二改進了的傳統清除行遷移的方法

　　． 執行$ORACLE_HOME/rdbms/admin目錄下的utlchainsql腳本創建chained_rows表

　　． 禁用所有其它表上關聯到此表上的所有限制

　　． 將表中的行遷移的row id放入臨時表中保存

　　． 刪除原來表中存在的行遷移的記錄行

　　． 從臨時表中取出並重新插入那些被刪除了的數據到原來的表中並刪除臨時表

　　． 啟用所有其它表上關聯到此表上的所有限制

　　這種算法是對傳統算法的一種改進對於使用這種算法來清除行遷移考慮到了表之間的關聯還可以靈活的利用的TOAD工具生成的表關聯信息是一種比較適合於清除行遷移的一種方法但是因為使用這種方法後來需要重建索引如果記錄數很大比如說上千萬條以上的記錄的表就不是很合適了因為這個重建索引的時間會很長是線性時間復雜度的而重建索引是會導致索引所在的表被鎖定的從而導致插入不了新的記錄重建索引的時間太長導致記錄長時間插入不了是會嚴重影響應用的甚至導致數據的丟失因此這個是使用這個方法前必須要考慮到的一個重要因素對於i以上的版本可以使用online的方法來重建索引這樣不會導致鎖表但是會有額外更多的開銷時間會很長再者因為這種方法在插入記錄和刪除記錄都是帶著索引的如果表上的行遷移比較多這樣耗時間會比較長而且占用資源也會比較大因此只適用於表上行遷移存在的比較少的表總的來說這種方法對於表記錄太多或者是表上的行遷移太多的情況都不是很適用比較適合表記錄少和表上行遷移都不太多的情況

　　以下是一個具體在生產數據庫上清除行遷移的例子在這之前已經調整過表的pctfree參數至一個合適的值了

　　SQL>select index_nameindex_typetable_name from user_indexes where table_name=TERMINAL;

　　INDEX_NAME INDEX_TYPE TABLE_NAME

　　

　　INDEX_TERMINAL_TERMINALCODE NORMAL TERMINAL

　　I_TERMINAL_ID_TYPE NORMAL TERMINAL

　　I_TERMINAL_OT_OID NORMAL TERMINAL

　　PK_TERMINAL_ID NORMAL TERMINAL

　　UI_TERMINAL_GOODIS_SSN NORMAL TERMINAL

　　SQL>select CONSTRAINT_NAMECONSTRAINT_TYPETABLE_NAME from USER_CONSTRAINTS where R_CONSTRAINT_NAME=PK_TERMINAL_ID;

　　CONSTRAINT_NAME C TABLE_NAME

　　

　　SYS_C R CONN

　　SQL>alter table CONN disable constraint SYS_C;

　　Table altered

　　SQL>CREATE TABLE TERMINAL_temp AS

　　SELECT * FROM TERMINAL

　　WHERE rowid IN

　　(SELECT head_rowid FROM chained_rows

　　WHERE table_name = TERMINAL);

　　Table created

　　SQL>select count(*) from TERMINAL_temp;

　　COUNT(*)

　　

　　

　　SQL>DELETE TERMINAL

　　WHERE rowid IN

　　(SELECT head_rowid

　　FROM chained_rows

　　WHERE table_name = TERMINAL);

　　 rows deleted

　　SQL>INSERT INTO TERMINAL SELECT * FROM TERMINAL_temp;

　　 rows created

　　SQL>alter table CONN disable constraint SYS_C;

　　Table altered

　　SQL>select count(*) from terminal;

　　COUNT(*)

　　

　　

　　SQL>truncate table chained_rows;

　　Table truncated

　　SQL>ANALYZE TABLE TERMINAL LIST CHAINED ROWS INTO chained_rows;

　　Table analyzed

　　SQL>select count(*) from chained_rows;

　　COUNT(*)

　　

　　

　　從上面過程中可以看出對TERMINAL這張表的行遷移清除耗時總共不到五分鐘的時間總體來說還是比較快的從我在生產數據庫中清除行遷移的經驗來說這種方法基本適用於大部分存在有行遷移的表

　　方法三使用TOAD工具清除行遷移的方法

　　． 備份要清除RM的表

　　RENAME table_name TO table_name_temp;

　　． Drop 所有其它表上關聯到table_name的外鍵限制

　　SELECT CONSTRAINT_NAMECONSTRAINT_TYPETABLE_NAME from USER_CONSTRAINTS where R_CONSTRAINT_NAME in (SELECT CONSTRAINT_NAME from USER_CONSTRAINTS where TABLE_NAME=table_name AND CONSTRAINT_TYPE=P);

　　ALTER TABLE table_name DROP CONSTRAINT XXXX;（XXXX為上述的查詢結果）

　　． 重建中被rename的表

　　CREATE TABLE table_name AS SELECT * FROM table_name_temp WHERE = ;

　　． 重建表中原來的數據

　　INSERT /*+ APPEND */ INTO table_name SELECT * FROM table_name_temp;

　　． 刪除在table_name_temp上的索引和關聯其他表的外鍵

　　． 在table_name上建立和原來一樣的索引主鍵和所有的外鍵限制

　　． 重新編譯相關的存儲過程函數和包

　　． 刪除表table_name_temp

　　對於使用這種方法來清除行遷移全部的代碼都是可以由TOAD工具來生成的由於此方法把表上的關聯考慮進去了也是一種比較的全面的考慮的一種清除方法而且在清除過程中重建了表和索引對於數據庫的存儲和性能上都有提高因為這種方法一開始是rename表為臨時表然後重建一個新表出來的因此需要兩倍的表的空間因此在操作之前一定要檢查要清除的表所在的表空間的free空間是否足夠但是也有一定的缺陷因為在新表中重新插入原來的數據後需要重建索引和限制因此在時間和磁盤的空間上都有比較大的開銷而且對於前台的應用可能會有一段時間的中斷當然這個中斷時間就主要是消耗在重建索引和重建限制上了而時間的長短跟需要重建索引和限制的多少以及表的記錄多少等等因素都有關系使用這種方法對於*小時要求的系統上清除行遷移不是很合適因為使用這種方法會導致系統可能有一段時間的停機如果系統的實時性比較高這種方法就不是很適用了

　　方法四使用EXP/IMP工具清除行遷移的方法

　　． 使用EXP導出存在有行遷移的表

　　． 然後TRUNCATE原來的表

　　． IMP開始導出的表

　　． 重建表上所有的索引（可選）

　　使用這種方法可以不用重建索引省去了這部分時間但是完成之後索引的使用效率不會很高最好是在以後逐步的在線重建索引這樣是可以不需要中斷業務的但是需要考慮的是IMP的時候會比較慢而且會占用比較大的IO應該選擇在應用不是很繁忙的時候做這項工作否則會對應用的正常運行產生較大的影響對於這種方法還存在有一個比較大的弊端就是在EXP表的時候要保證該表是沒有數據的更新或者是只讀狀態的不能對表有插入或者更新操作否則會導致數據的丟失

　　SQL> select count(*) from test;

　　COUNT(*)

　　

　　

　　SQL> truncate table chained_rows;

　　Table truncated

　　SQL> analyze table test LIST CHAINED ROWS INTO chained_rows;

　　Table analyzed

　　SQL> select count(*) from chained_rows;

　　COUNT(*)

　　

　　

　　$ exp allan/allan file=testdmp tables=test

　　Export: Release Production on Sun Jun ::

　　Copyright (c) Oracle Corporation All rights reserved

　　Connected to: Oraclei Enterprise Edition Release Production

　　With the Partitioning OLAP and Oracle Data Mining options

　　JServer Release Production

　　Export done in ZHSGBK character set and ALUTF NCHAR character set

　　About to export specified tables via Conventional Path

　　 exporting table TEST rows exported

　　Export terminated successfully without warnings

　　$ sqlplus allan/allan

　　SQL*Plus: Release Production on Sun Jun ::

　　Copyright (c) Oracle Corporation All rights reserved

　　Connected to:

　　Oraclei Enterprise Edition Release Production

　　With the Partitioning OLAP and Oracle Data Mining options

　　JServer Release Production

　　SQL> truncate table test;

　　Table truncated

　　SQL> exit

　　Disconnected from Oraclei Enterprise Edition Release Production

　　With the Partitioning OLAP and Oracle Data Mining options

　　JServer Release Production

　　$ imp allan/allan file=testdmp full=y ignore=y buffer=

　　Import: Release Production on Sun Jun ::

　　Copyright (c) Oracle Corporation All rights reserved

　　Connected to: Oraclei Enterprise Edition Release Production

　　With the Partitioning OLAP and Oracle Data Mining options

　　JServer Release Production

　　Export file created by EXPORT:V via conventional path

　　import done in ZHSGBK character set and ALUTF NCHAR character set

　　 importing ALLANs objects into ALLAN

　　 importing table TEST rows imported

　　Import terminated successfully without warnings

　　$ sqlplus allan/allan

　　SQL*Plus: Release Production on Sun Jun ::

　　Copyright (c) Oracle Corporation All rights reserved

　　Connected to:

　　Oraclei Enterprise Edition Release Production

　　With the Partitioning OLAP and Oracle Data Mining options

　　JServer Release Production

　　SQL> select count(*) from test;

　　COUNT(*)

　　

　　

　　SQL> select index_name from user_indexes where table_name=TEST;

　　INDEX_NAME

　　

　　OBJ_INDEX

　　SQL> alter index OBJ_INDEX rebuild online;

　　Index altered

　　SQL> truncate table chained_rows;

　　Table truncated

　　SQL> analyze table test LIST CHAINED ROWS INTO chained_rows;

　　Table analyzed

　　SQL> select count(*) from chained_rows;

　　COUNT(*)

　　

　　

　　方法五使用MOVE命令來清除行遷移的方法

　　． 查看要清除行遷移的表所在的表空間

　　Select table_nametablespace_name from user_tables where table_name=table_name;

　　． 查看要清除行遷移的表上的具體索引

　　select index_nametable_name from user_indexes where table_name＝table_name;

　　． Move要清除RM的表到指定的表空間中去

　　alter table table_name move tablespace tablespace_name;

　　． 重建表上的所有索引

　　alter index index_name rebuild;

　　這種方法適用於i及其以上的數據庫版本主要是利用數據庫的一個MOVE命令來實現行遷移的清除的MOVE命令的實質其實就是INSERT … SELECT的一個過程在MOVE表的過程中是需要兩倍的原來的表大小的因為中間過程是要保留原來的舊表的新表創建完成後舊表就被刪除並釋放空間了MOVE的時候要注意後面一定要加上表空間參數所以必須要先知道表所在的表空間因為MOVE表之後需要重建索引所以之前要確定表上的所有的索引

　　這種方法對於表記錄數很大或者表上索引太多的情況不太適用因為本身的MOVE就會很慢 而且MOVE表的時候會要鎖定表時間長了會導致對表的其他操作出現問題導致數據插入不了丟失數據MOVE表後還要重建索引索引太多了的話重建的時間也會太長再者這個方法也比較消耗資源因此強烈建議在業務不繁忙的時候再執行

　　以下是一個具體在生產數據庫上清除行遷移的例子在這之前已經調整過表的pctfree參數至一個合適的值了

　　SQL>ANALYZE TABLE SERVICE LIST CHAINED ROWS INTO chained_rows;

　　Table analyzed

　　SQL>SELECT count(*) from chained_rows;

　　COUNT(*)

　　

　　

　　SQL>select table_nametablespace_name from user_tables where table_name=SERVICE;

　　TABLE_NAME TABLESPACE_NAME

　　

　　SERVICE DATA

　　SQL>select index_nametable_name from user_indexes where table_name=SERVICE;

　　INDEX_NAME TABLE_NAME

　　

　　I_SERVICE_ACCOUNTNUM SERVICE

　　I_SERVICE_DATEACTIVATED SERVICE

　　I_SERVICE_SC_S SERVICE

　　I_SERVICE_SERVICECODE SERVICE

　　PK_SERVICE_SID SERVICE

　　SQL>select count(*) from SERVICE;

　　COUNT(*)

　　

　　

　　SQL>alter table SERVICE move tablespace DATA;

　　Table altered

　　SQL>alter index I_SERVICE_ACCOUNTNUM rebuild;

　　Index altered

　　SQL>alter index I_SERVICE_DATEACTIVATED rebuild;

　　Index altered

　　SQL>alter index I_SERVICE_SC_S rebuild;

　　Index altered

　　SQL>alter index I_SERVICE_SERVICECODE rebuild;

　　Index altered

　　SQL>alter index PK_SERVICE_SID rebuild;

　　Index altered

　　SQL>truncate table chained_rows;

　　Table truncated

　　SQL>ANALYZE TABLE SERVICE LIST CHAINED ROWS INTO chained_rows;

　　Table analyzed

　　SQL>SELECT count(*) from chained_rows;

　　COUNT(*)

　　

　　

　　利用MOVE命令來清除行遷移執行的命令都相對比較的簡單上面的例子中清除表SERVCIE中的行遷移的時間大概在五分鐘左右其中move命令執行的時間為不到兩分鐘也就是鎖表的時間大概是不到兩分鐘對於大多數的應用來說一般問題都是不大的放在系統閒的時候執行基本上不會對應用產生什麼太多的影響

　　方法六對於一些行遷移數量巨大而且表記錄數巨大的表的行遷移的清除方法

　　． 使用TOAD工具或者別的方法獲取存在有大量行遷移並且表記錄很大的表的重建表的SQL然後保存為腳本

　　． 使用RENAME命令將原始表重命名為一個備份表然後刪除別的表對原始表上的限制以及原始表上的外鍵和索引

　　． 利用中生成的腳本重建原始表以及表上的限制外鍵索引等對象

　　． 然後按表模式導出中備份的表然後導入到另外的一個臨時中轉的數據庫庫中因為表的名字已經改變所以導入後需要RENAME表為原來的名字然後重新導出最後再導入到原來的數據庫中

　　這種方法主要是用來針對一些數據量比較大並且表上的行遷移也比較多的表的行遷移清除對於這些大表的行遷移的清除正常來說都需要停應用一段較長時間才能夠清除掉讓人感覺比較的頭疼對於*小時的應用來說down機的時間越長損失則越大當然是要盡量的減短down機的時間但是因為表本身比較大不管怎樣做什麼操作都是會比較耗費時間和資源的但是如果應用在某段時間內主要是以插入數據為主更新數據和刪除數據都很少的因此可以考慮可以采用這麼一種方法先重命名表然後重新建立一個和原來一樣的表用來保證之後的應用的數據是可以正常插入的從而使應用不用停很久因為重建一個沒有任何數據的表結構的過程是很短暫的大概需要幾秒鐘的時間而重建好表了後就能保證應用能夠正常的寫入數據從而使應用幾乎不用停頓然後把開始重命名的原始表按表模式導出因為表的名字已經被改變因此需要一個臨時庫來導入這些數據然後重命名回原來的名字然後按原來的表名導出後再重新導入原始數據庫這樣操作起來雖然會比較麻煩但是卻是一種很有效很實際的方法速度也很快導出後導入因為本身表結構已經建立好了不需要其他任何的多的操作而且最關鍵的是這種方法所需要的down機時間是最短的

　　SQL>ALTER TABLE USERPAY RENAME TO PAY_X ;

　　然後導出PAY_X表;

　　$ exp USER/USER file=PAY_Xdmp tables=PAY_X

　　SQL>ALTER TABLE USERBATCHPAYMENTDETAIL DROP CONSTRAINT FK_BATCHPAYMENTDETAIL_OPAYID ;

　　SQL>ALTER TABLE USERDEPOSITCLASSIFY DROP CONSTRAINT FK_DEPOSITCLASSIFY ;

　　SQL>ALTER TABLE USERDEPOSITCREDITLOG DROP CONSTRAINT FK_DEPOSITCREDITLOG ;

　　SQL>ALTER TABLE USERDEPOSIT DROP CONSTRAINT SYS_C ;

　　SQL>ALTER TABLE USERPAY_X DROP CONSTRAINT SYS_C ;

　　SQL>DROP INDEX USERI_PAY_STAFFID ;

　　SQL>CREATE TABLE USERPAY

　　(

　　PAYID NUMBER()

　　ACCOUNTNUM NUMBER()

　　TOTAL NUMBER()

　　PREVPAY NUMBER()

　　PAY NUMBER()

　　STAFFID NUMBER()

　　PROCESSDATE DATE

　　PAYNO CHAR()

　　TYPE CHAR() DEFAULT

　　PAYMENTMETHOD CHAR() DEFAULT

　　PAYMENTMETHODID VARCHAR()

　　BANKACCOUNT VARCHAR()

　　PAYMENTID NUMBER()

　　STATUS CHAR() DEFAULT

　　MEMO VARCHAR()

　　SERVICEID NUMBER()

　　CURRENTDEPOSITID NUMBER()

　　SHOULDPROCESSDATE DATE DEFAULT sysdate

　　ORIGINALEXPIREDATE DATE

　　ORIGINALCANCELDATE DATE

　　EXPIREDATE DATE

　　CANCELDATE DATE

　　DEPOSITTYPE CHAR()

　　)

　　TABLESPACE USER

　　PCTUSED

　　PCTFREE

　　INITRANS

　　MAXTRANS

　　STORAGE (

　　INITIAL K

　　NEXT K

　　MINEXTENTS

　　MAXEXTENTS

　　PCTINCREASE

　　FREELISTS

　　FREELIST GROUPS

　　BUFFER_POOL DEFAULT

　　)

　　NOLOGGING

　　NOCACHE

　　NOPARALLEL;

　　SQL>CREATE INDEX USERI_PAY_STAFFID ON USERPAY

　　(STAFFID)

　　NOLOGGING

　　TABLESPACE USER

　　PCTFREE

　　INITRANS

　　MAXTRANS

　　STORAGE (

　　INITIAL K

　　NEXT K

　　MINEXTENTS

　　MAXEXTENTS

　　PCTINCREASE

　　FREELISTS

　　FREELIST GROUPS

　　BUFFER_POOL DEFAULT

　　)

　　NOPARALLEL;

　　SQL>CREATE UNIQUE INDEX USERPK_PAY_ID ON USERPAY

　　(PAYID)

　　NOLOGGING

　　TABLESPACE USER

　　PCTFREE

　　INITRANS

　　MAXTRANS

　　STORAGE (

　　INITIAL K

　　NEXT K

　　MINEXTENTS

　　MAXEXTENTS

　　PCTINCREASE

　　FREELISTS

　　FREELIST GROUPS

　　BUFFER_POOL DEFAULT

　　)

　　NOPARALLEL;

　　SQL>ALTER TABLE USERPAY ADD (

　　FOREIGN KEY (STAFFID)

　　REFERENCES USERSTAFF (STAFFID));

　　SQL>ALTER TABLE USERDEPOSITCLASSIFY ADD

　　CONSTRAINT FK_DEPOSITCLASSIFY

　　FOREIGN KEY (PAYID)

　　REFERENCES USERPAY (PAYID) ;

　　SQL>ALTER TABLE USERDEPOSITCREDITLOG ADD

　　CONSTRAINT FK_DEPOSITCREDITLOG

　　FOREIGN KEY (PAYID)

　　REFERENCES USERPAY (PAYID) ;

　　SQL>ALTER FUNCTION USERGENERATEPAYNO COMPILE ;

　　SQL>ALTER PROCEDURE USERENGENDERPRVPAY COMPILE ;

　　SQL>ALTER PROCEDURE USERISAP_ENGENDERPRVPAY COMPILE ;

　　SQL>ALTER PROCEDURE USERSPADDCREDITDEPOSIT COMPILE ;

　　SQL>ALTER PROCEDURE USERSPADDDEPOSITWITHOUTCARD COMPILE ;

　　SQL>ALTER PROCEDURE USERSPADJUSTLWDEPOSIT COMPILE ;

　　……

　　然後將導出的表PAY_X的dmp文件導入一個臨時的數據庫中然後在臨時數據庫中將其表名重新命名為PAY再按表模式將其導出

　　imp USER/USER file= PAY_xdmp tables=PAY ignore=y

　　SQL>rename PAY_X to PAY;

　　$ exp USER/USER file=PAYdmp tables=PAY

　　最後將這個dmp文件導入正式的生產數據庫中即可

　　以上的過程在重建好PAY表後整個應用就恢復正常了而重命名表後重建表的時間是非常之短的我測試的時間大概是在幾分鐘之內就可以做完了新數據就可以插入表了剩下的工作就是將舊的數據導入數據庫這個工作的時間要求上就沒有那麼高了因為應用已經正常了一般來說利用晚上業務不忙的時候都可以把一張表的數據導入完成的

　　以上的六種清除行遷移的方法各有各自的優缺點分別適用於不同的情況下使用利用以上的幾種清除行遷移的方法基本上就能完全清除掉系統中的存在的行遷移了當然具體的生產環境中還需要具體問題具體分析的針對不同類型的系統系統中不同特點的表采用不同的清除方法盡量的減少停數據庫的時間以保證應用的不間斷穩定運行

From:http://tw.wingwit.com/Article/program/Oracle/201311/18114.html