Oracle重建表(CTAS+rename)注意事項總結

一、概述 前一段時間，有一個DBA朋友在完成重建表(rename)工作後，第二天早上業務無法正常運作，出現資料無法插入的限制和錯誤，後來分析才發現，錯誤的原因是使用rename方式重建表以後，其它引用這個表的外鍵限制指向沒有重新定義到這個重建的新表中，進而導緻這些表在插入新資料時，違反資料完整性限制，導緻資料無法正常插入。影響了業務大概有1個多小時，真是一次血淋淋的教訓啊。 使用rename方式重建表是我們日常DBA維護工作中經常使用的一種方法，因為CTAS+rename這種配合方式，非常實用和高效。很多DBA朋友應該也都是用過rename方式重建表，而且重建完成以後也都一切正常，沒有引起過問題。但是，我想說的是，使用rename重建表後，具體需要完成哪些掃尾工作你真的清楚嗎？？ 這篇文章主要就是歸納當我們使用rename方式重建表後，需要進行哪些掃尾工作，如果你還不是很清楚，一定要仔細 閱讀 這篇文章，同時在以後的重建表工作中矯正過來，否則，問題遲早有一天會降臨到你的身邊！ 

二、重建表的方式 這裡先不談其它，僅僅說一下重建表的方法。如下: 1、為了確定所有表字段、字段類型、長度完全一樣，我一般不建議使用CTAS方式來重建表。 2、一般我都是使用下面兩種方法中的一個，來抽取表的定義 select dbms_metadata.get_ddl('TABLE',upper('&i_table_name'),upper('&i_owner')) from dual; 使用PL/SQL developer類似這樣的工具，來檢視表定義語句 3、重建立一張_old類型的表（根據上面的抽取的表定義），然後使用insert xx select xxx 方式完成資料的轉換 4、最後使用rename方式倒換這兩張表的名字 

三、重建表注意事項 索引重建： 這裡最關鍵的是，重建後索引的名字是否必須和以前的一樣，如果需要一樣，則必須将目前使用的索引名字先rename，否則建立的時候會出現索引名字已經存在的錯誤,如下： select 'alter index ' || owner || '.' || index_name || ' rename to ' || substr(index_name, 1, 26) || '_old;' from dba_indexes a where a.table_owner = 'DBMON' AND A.table_name = 'DH_T'; 

依賴對象重建： 一般可以使用如下方式完成 select 'alter '||decode(type,'PACKAGE BODY','PACKAGE',type)||' '||owner||'.'||name||' compile;' from dba_dependencies a where a.referenced_name = 'DH_T' and a.referenced_owner = 'DBMON';  注意： 1、這裡重建的隻是直接依賴對象，必須考慮那些間接依賴的對象（例如 view1依賴A表，view2依賴view1），查找方法和上面差不多 2、如果這些依賴對象中存在一些私有對象（例如dblink等），我們用DBA使用者重新編譯是會出現編譯錯誤，對于這種對象，必須以對應對象的所屬者才能編譯成功。(也可用用10g以後新出現的代理權限來完成這類任務！） 針對PL/SQL代碼（包、函數、過程等），是否存在私有對象的查找方法，如下： select * from dba_source a where (a.owner, a.name) in (select owner, name from dba_dependencies b where b.referenced_name = 'DH_T' and b.referenced_owner = 'DBMON') and a.TEXT like '%@%';  針對視圖中是否存在私有對象的查找方法，如下(由于是long類型，必須得一個一個檢視）： select * from dba_views a where (a.owner, a.view_name) in (select owner, name from dba_dependencies b where b.referenced_name = 'DH_T' and b.referenced_owner = 'DBMON' and b.type = 'VIEW') 

權限重建： 可以使用如下語句 select 'grant ' || PRIVILEGE || ' on ' || owner || '.' || table_name || ' to ' || grantee || ';' from dba_tab_privs where table_name = upper('&i_table_name') and owner = upper('&i_owner'); 

外鍵重建： 對于外鍵，現在的業務資料邏輯很多都是在應用層來實作，是以表上的外鍵可能都非常少，是以，導緻很多DBA都忘記需要檢查和重建這一部分了，進而導緻業務出現問題，本章最開始說的故障案例就是因為沒有重建外鍵而引起，是以我們一定要提高警惕。可以使用如下語句檢視，哪些表引用了重建表 select a.table_name, a.owner, a.constraint_name, a.constraint_type, a.r_owner, a.r_constraint_name,--被外鍵引用的限制名 b.table_name --被外鍵引用的表名 from dba_constraints a, dba_constraints b where a.constraint_type = 'R' and a.r_constraint_name = b.constraint_name and a.r_owner = b.owner and b.table_name = 'FSPARECEIVEBILLTIME' and b.owner=''; 

物化視圖： 另外一個非常重要的依賴對象就是物化視圖，一般來說，rename表以後，物化視圖是不會有問題的，再次重新整理時會自動編譯，但是這可能會影響優化其選擇執行計劃，是以，建議手工直接編譯這些失效的物化視圖，如下 alter MATERIALIZED VIEW DH_T_MV compile; 備注，其實這步已經包含在依賴對象重建部分了，單獨拿出來是因為這個依賴對象非常重要，不容有任何意外 

物化視圖日志： 物化視圖日志是為了快速重新整理準備的，而且從dba_dependencies 這張依賴表中無法查找出來的，但是，對于這個對象，我們一定要保持謹慎和敬畏，因為如果表上存在物化視圖日志對象的話，那麼這張表無法完成rename（在一個變更的晚上，其它什麼都OK了，突然遇到一個這樣的問題，還得找開發确認，是非常被動的，整個變更很有可能因為這個無法确認而取消），會直接報錯，查找表上的物化視圖日志對象方法如下： select master,log_table from user_mview_logs a where master in ('DH_T'); 

備注： 1、我們可能還需要關注表字段類型，那些LOB、long字段都是我們重建表是需要考慮的 2、還有就是重建表時我們可能都會使用parallel+nologging模式來加快速度，一定要記得在重建完成後将這些屬性修改回來。（以前遇到過一個案例，未将parallel屬性改回來，導緻執行計劃選用并行，最終導緻資源很快耗盡，CPU100%） 3、還有一些同步機制，如果同步依賴rowid，由于重建表rowid會該表，可能造成實時同步失敗，這些都是我們需要考慮的最後，在工作完成後，檢查一下所有對象的有效性是一個不錯的方案。（建議在重建前儲存快照，重建後與前面的快照比較）上面講述的都是一些我們最常用的對象，其它一些很少使用的對象這裡就不概述了

原文位址：http://www.2cto.com/database/201407/313687.html