Oracle で export/import した後で制約エラーがでたときの対処方法
開発サーバのリースが切れるので新しいサーバをリースしました。
せっかくなので、本番DBから最新のデータを export して開発サーバに import してみました。3年に一度のペースの作業なので、以前のことなんて完全に忘れてしまっています。毎度同じエラーで時間を食ってる気がするのでココに備忘録として残しておこうと思う次第です。
毎度でくわすエラーは制約エラーなどの import 時のエラー。本番DBなので稼働中に export するので、手っ取り早くユーザ単位で export しちゃうんですが、タイミングによってはすでに export 済みのマスターテーブルの後に、外部キー制約してるテーブルが export される場合では、タイミング次第ではマスターにないキーを持つレコードが追加されて可能性があります。
そんな export ファイルを import すると、当然のように参照制約でエラーがでてしまいます。本来は、テーブル単位で順序を考えながら export の設定ファイルを書いてあげるれば、少なくとも参照制約のエラーはでないのですが、それでも整合性のある状態にクリーニングが必要です。もっとも、本番DBが稼働している最中に export するからこんな不整合が発生するわけですが。
いつも export した後にはこんな SQL を使って制約を有効化しています。
DECLARE BEGIN FOR CUR IN (SELECT table_name,constraint_name FROM user_constraints) LOOP EXECUTE IMMEDIATE 'ALTER TABLE '||CUR.table_name||' ENABLE CONSTRAINT '||CUR.constraint_name; END LOOP; END; /
エラーがでたならば、そのテーブルに不整合が発生しているので、不要なレコードを delete します。これを繰り返せば、そのうち上記 SQL がエラーなく終了するようになります。
つぎに、プロシージャやファンクションが INVALID 状態になっているものを再コンパイルします。まずは STATUS を確認します。
SET LINE 1000 SET PAGES 1000 SELECT OBJECT_TYPE,OBJECT_NAME,STATUS FROM USER_OBJECTS WHERE OBJECT_TYPE in ('PROCEDURE', 'FUNCTION') ORDER BY STATUS, OBJECT_TYPE;
結果はこんな感じ。
OBJECT_TYPE OBJECT_NAME STATUS ------------------- ------------------------------------ ------- FUNCTION USER_FUNCTION_001 INVALID FUNCTION USER_FUNCTION_002 INVALID .... FUNCTION USER_FUNCTION_00m VALID FUNCTION USER_FUNCTION_00n VALID .... PROCEDURE USER_PROCEDURE_001 INVALID PROCEDURE USER_PROCEDURE_002 INVALID .... PROCEDURE USER_PROCEDURE_00m VALID PROCEDURE USER_PROCEDURE_00n VALID
import の順番やら制約エラーやらで INVALID 状態のものが結構でちゃいます。もちろんこのままでは使い物にならないので、これまた以下の SQL で再コンパイルしちゃいます。これまた VALID になる順序とかの関係で一回で全てが正常に再コンパイルできない可能性もありますが、エラーがでた場合はこの SQL を数回繰り返せばそのうち全てコンパイルが通るようになると思います。
SET SERVEROUTPUT ON SIZE 1000000 BEGIN FOR cur_rec IN ( SELECT OBJECT_NAME, OBJECT_TYPE, DECODE(OBJECT_TYPE, 'PACKAGE', 1, 'PACKAGE BODY', 2, 2) AS RECOMPILE_ORDER FROM USER_OBJECTS WHERE OBJECT_TYPE IN ('PACKAGE', 'PACKAGE BODY') AND STATUS = 'INVALID' ORDER BY 3 ) LOOP BEGIN IF cur_rec.object_type = 'PACKAGE BODY' THEN EXECUTE IMMEDIATE 'ALTER PACKAGE "' || cur_rec.object_name || '" COMPILE BODY'; ElSE EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type || ' "' || cur_rec.object_name || '" COMPILE'; END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line(cur_rec.object_type || ' : ' || cur_rec.object_name); END; END LOOP; END; /
ちなみに、上記 SQL では以下のような SQL を自動的に発行してくれています。もちろん手動で下記のコマンドを実行してもOKです。
ALTER VIEWCOMPILE; ALTER FUNCTION COMPILE; ALTER PROCEDURE COMPILE; ALTER PACKAGE COMPILE; ALTER PACKAGE COMPILE BODY; ALTER TRIGGER COMPILE;
最後に USER_OBJECTS のステータスを確認しておく。STATUS が全て VALID になっていたらOK!
SELECT OBJECT_NAME, OBJECT_TYPE, STATUS FROM USER_OBJECTS;
そうだ、まだあった。インデックスがレコードの不整合で使えない状態になっている場合もありました。次の SQL で調査できます。
SELECT INDEX_NAME,STATUS FROM USER_INDEXES WHERE STATUS!='VALID' ORDER BY INDEX_NAME; INDEX_NAME STATUS ------------------------------ -------- PK_LOG_DATA UNUSABLE ・・・・
STATUS が VALID で無い場合は不整合のレコードを削除してインデックスを再構築する必要があります。たとえば今回の例で言うとこんな感じの作業をしています。
SQL> DELETE FROM LOG_DATA WHERE CATEGORY='top'; DELETE FROM LOG_DATA WHERE CATEGORY='top' * 行1でエラーが発生しました。: ORA-01502: 索引'DB.PK_LOG_DATA'またはそのパーティションが使用不可の状態です。 -- ここでどうもインデックスが壊れているっぽいことに気がついたので調査してみる。 SQL> ALTER TABLE LOG_DATA ADD ( CONSTRAINT PK_LOG_DATA PRIMARY KEY (ID,CATEGORY,NUM) ); 2 3 CONSTRAINT PK_LOG_DATA PRIMARY KEY (ID,CATEGORY,NUM) * 行2でエラーが発生しました。: ORA-14063: 使用できない索引が一意/主制約キーに存在します -- どうも不整合レコードが存在するようだと気がついたので、レコード削除 -- まずは不正レコードの抽出 SELECT ID,CATEGORY,NUM FROM LOG_DATA GROUP BY ID,CATEGORY,NUM HAVING COUNT(1)>1; -- 不正レコードの削除 DELETE FROM LOG_DATA GROUP BY ID,CATEGORY,NUM HAVING COUNT(1)>1; COMMIT;
不整合を取り除いた後は、全てのテーブルのインデックスを再構築する SQL をががっと流す。
DECLARE BEGIN /* インデックスを再構築&アナライズ */ FOR CUR IN (SELECT INDEX_NAME FROM USER_INDEXES WHERE INDEX_NAME not like 'SYS%') LOOP EXECUTE IMMEDIATE 'ALTER INDEX "' ||CUR.INDEX_NAME||'" REBUILD ONLINE NOLOGGING'; EXECUTE IMMEDIATE 'ANALYZE INDEX "' ||CUR.INDEX_NAME||'" ESTIMATE STATISTICS SAMPLE 30 PERCENT'; END LOOP; /* ユーザーテーブルのアナライズ */ FOR CUR IN (SELECT TABLE_NAME FROM USER_TABLES) LOOP EXECUTE IMMEDIATE 'ANALYZE TABLE ' ||CUR.TABLE_NAME||' ESTIMATE STATISTICS SAMPLE 30 PERCENT'; END LOOP; END; /
これで export/import の際に発生するエラーの大部分は解消できていると思います。その他エラーは今回は発生しなかったので、備忘録はここまで。
コメントやシェアをお願いします!