昔のOracle から 新しいOracleデータ移行

いやぁ…時間が掛かった…

解決方法の書いてあるURL：
http://blog.suz-lab.com/2011/06/oracle-rdsal32utf8_17.html



環境
移行元：
Oracle8i
NLS_LANGUAGE：AMERICAN
NLS_TERRITORY：AMERICA
NLS_ISO_CURRENCY：AMERICA
NLS_CHARACTERSET：JA16SJIS
NLS_DATE_LANGUAGE：AMERICAN
NLS_COMP：BINARY
NLS_NCHAR_CHARACTERSET：JA16SJIS
NLS_RDBMS_VERSION：8.1.7.0.0

移行先：
Oracle11g XE
NLS_LANGUAGE：AMERICAN
NLS_TERRITORY：AMERICA
NLS_ISO_CURRENCY：AMERICA
NLS_CHARACTERSET：AL32UTF8
NLS_DATE_LANGUAGE：AMERICAN
NLS_COMP：BINARY
NLS_LENGTH_SEMANTICS：BYTE
NLS_NCHAR_CONV_EXCP：FALSE
NLS_NCHAR_CHARACTERSET：AL16UTF16
NLS_RDBMS_VERSION：11.2.0.2.0


環境を取ってきた該当SQL:
SELECT PARAMETER,VALUE FROM NLS_DATABASE_PARAMETERS

エクスポートコマンド：
exp userid=test/test@//localhost:1521/aiueo file=aiueo.dmp buffer=10485760 grants=y indexes=y compress=y full=n log=aiueo.log direct=y

インポートコマンド：
IMP USERID=test/test@xe FILE=test.dmp LOG=test FULL=Y COMMIT=Y



普通にこれでインポートすると、エラーが出ます。
ORA-12899: 列"TEST"."TEST_TABLE"."TEST_COLUMN"の値が大きすぎます(実際: 150、最大: 100)

と言う事で探し回った先が最初のURLです。


で、私が使用するように書き直してみました。
起動バッチ.bat

*******************************************
rem データ以外のインポート
IMP USERID=TEST/TEST@xe FILE=aiueo.dmp LOG=aiueo-1 FULL=Y COMMIT=Y rows=N indexes=N >> inportLog.log
rem テーブルのchar型を正常値に変更
SQLPLUS TEST/TEST@xe @UPDATE_TABLE.SQL >> inportLog.log
rem 制約をOFFに設定
SQLPLUS TEST/TEST@xe @CONSTRAINT_DISABLE.SQL >> inportLog.log
rem データのインポート
IMP USERID=TEST/TEST@xe FILE=aiueo.dmp LOG=aiueo-2 FULL=Y COMMIT=Y ignore=Y >> inportLog.log
rem 制約をONに設定
SQLPLUS TEST/TEST@xe @CONSTRAINT_ENABLE.SQL >> inportLog.log
PAUSE
*******************************************

UPDATE_TABLE.SQL

*******************************************
SET LINESIZE 2000;
SET SERVEROUTPUT ON;
DECLARE
  ddl VARCHAR(2000);
BEGIN
  FOR cur IN (
    SELECT USER_TAB_COLUMNS.TABLE_NAME
         , USER_TAB_COLUMNS.COLUMN_NAME
         , USER_TAB_COLUMNS.DATA_TYPE
         , USER_TAB_COLUMNS.DATA_LENGTH
    FROM USER_TAB_COLUMNS, USER_TABLES
    WHERE USER_TAB_COLUMNS.TABLE_NAME = USER_TABLES.TABLE_NAME
      AND (USER_TAB_COLUMNS.DATA_TYPE = 'VARCHAR2' OR USER_TAB_COLUMNS.DATA_TYPE = 'CHAR')
  ) LOOP
    ddl := 'ALTER TABLE ' || cur.TABLE_NAME || ' MODIFY (' || cur.COLUMN_NAME || ' ' || cur.DATA_TYPE || '(' || cur.DATA_LENGTH || ' CHAR))';
    DBMS_OUTPUT.PUT_LINE(ddl);
    EXECUTE IMMEDIATE ddl;
  END LOOP;
END;
/

DECLARE
  username VARCHAR(2000);
  ddl VARCHAR(2000);
BEGIN
  SELECT USER INTO username FROM DUAL;
  FOR cur IN (
    SELECT USER_TAB_STATISTICS.TABLE_NAME
    FROM USER_TAB_STATISTICS
  ) LOOP
    DBMS_OUTPUT.PUT_LINE(username || ' ' || cur.TABLE_NAME);
    DBMS_STATS.UNLOCK_TABLE_STATS(username, cur.TABLE_NAME);
  END LOOP;
END;
/
EXIT
/
*******************************************

CONSTRAINT_DISABLE.SQL

*******************************************
SET LINESIZE 2000;
SET SERVEROUTPUT ON;
DECLARE
  ddl VARCHAR(2000);
BEGIN
  FOR cur IN (
    SELECT constraint_name,
           constraint_type,
           table_name,
           status 
      FROM user_constraints
     ORDER BY constraint_name
  ) LOOP
    ddl := 'ALTER TABLE ' || cur.table_name || ' DISABLE CONSTRAINT ' || cur.constraint_name;
    DBMS_OUTPUT.PUT_LINE(ddl);
    EXECUTE IMMEDIATE ddl;
  END LOOP;
END;
/
EXIT
/
*******************************************

CONSTRAINT_ENABLE.SQL

*******************************************
SET LINESIZE 2000;
SET SERVEROUTPUT ON;
DECLARE
  ddl VARCHAR(2000);
BEGIN
  FOR cur IN (
    SELECT constraint_name,
           constraint_type,
           table_name,
           status 
      FROM user_constraints
     ORDER BY constraint_name DESC
  ) LOOP
    ddl := 'ALTER TABLE ' || cur.table_name || ' ENABLE CONSTRAINT ' || cur.constraint_name;
    DBMS_OUTPUT.PUT_LINE(ddl);
    EXECUTE IMMEDIATE ddl;
  END LOOP;
END;
/
EXIT
/
*******************************************

ここまで読む人はいないだろうけど、とりあえず説明
起動バッチ.bat
　→　ダブルクリックで起動する奴です。
　　　この順番が重要です。

UPDATE_TABLE.SQL
　→　URLに書いてある重要なSQL
　　　これを実行することで、INSERT時のエラーが無くなります。

CONSTRAINT_DISABLE.SQL
　→　制約をOFFにするSQL
　　　SQLの中の「ORDER BY」は書き直してください。
　　　私のシステムでは、この順番じゃないとすべてOFFに出来ませんでした。

CONSTRAINT_ENABLE.SQL
　→　制約をONにするSQL
　　　SQLの中の「ORDER BY」は書き直してください。
　　　順番は気にしなくてもONに出来ましたが、とりあえずつけてます。



検索ワード：Oracle インポート エクスポート imp exp バージョン違い エラー UTF-8 UTF8 SJIS ORA-12899 NLS_LANG 変更 やり方 方法 インポート出来ない SHIFT-JISからUTF-8の変更 サイズが増える