Oracle で export/import した後で制約エラーがでたときの対処方法

開発サーバのリースが切れるので新しいサーバをリースしました。

せっかくなので、本番ＤＢから最新のデータを export して開発サーバに import してみました。３年に一度のペースの作業なので、以前のことなんて完全に忘れてしまっています。毎度同じエラーで時間を食ってる気がするのでココに備忘録として残しておこうと思う次第です。

毎度でくわすエラーは制約エラーなどの import 時のエラー。本番ＤＢなので稼働中に export するので、手っ取り早くユーザ単位で export しちゃうんですが、タイミングによってはすでに export 済みのマスターテーブルの後に、外部キー制約してるテーブルが export される場合では、タイミング次第ではマスターにないキーを持つレコードが追加されて可能性があります。

そんな export ファイルを import すると、当然のように参照制約でエラーがでてしまいます。本来は、テーブル単位で順序を考えながら export の設定ファイルを書いてあげるれば、少なくとも参照制約のエラーはでないのですが、それでも整合性のある状態にクリーニングが必要です。もっとも、本番ＤＢが稼働している最中に 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 を自動的に発行してくれています。もちろん手動で下記のコマンドを実行してもＯＫです。 

ALTER VIEW  COMPILE;
ALTER FUNCTION  COMPILE;
ALTER PROCEDURE  COMPILE;
ALTER PACKAGE  COMPILE;
ALTER PACKAGE  COMPILE BODY;
ALTER TRIGGER  COMPILE;

最後に USER_OBJECTS のステータスを確認しておく。STATUS が全て VALID になっていたらＯＫ！ 

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 の際に発生するエラーの大部分は解消できていると思います。その他エラーは今回は発生しなかったので、備忘録はここまで。

- スポンサーリンク -

関連する記事＆スポンサーリンク