2011年6月17日金曜日

"Oracle RDS"に文字コードがAL32UTF8以外のデータ(日本語含む)をインポート

スズキです。

コチラで紹介した通り、"Oracle RDS"の文字コードはAL32UTF8に固定されています。
ですので、他の文字コードのOracleデータベースのデータを移行(exp/imp)するときに、
インポート(imp)時に下記のようなエラーが発生することがあります。

ORA-12899: 列"SUZLAB"."SUZLAB_TABLE"."SUZ_LAB_COLUMN"の値が大きすぎます(実際: 150、最大: 100)

これは、Oracleの文字型の列のサイズ指定はデフォルトではバイト単位で指定するので、
同じ一文字でも日本語などでは、例えばもとは2バイトでも移行時AL32UTF8に
変換されるときに3バイトになってしまい、結果として列に指定されたサイズ(バイト単位)を
超えてしまうことが原因です。

ただOracle(Oracle RDS)には、コチラで紹介したとおり"nls_length_semantics"という
パラメータを調整することにより、文字型の列のサイズをバイト単位を文字数単位に
することができます。

しかし、このパラメータは新規に作成するテーブルに対して有効になり、
インポート(imp)で作成するテーブルはバイト単位のままでした。

ということで、"Oracle RDS"にAL32UTF8以外のデータをインポートするには、
下記の手順で行う必要があります。

(1) スキーマのみインポート(インデックスも作成しない)
(2) 全てのテーブルの文字型の列のサイズをバイト単位から文字数単位に再定義
(3) 統計情報のロックを解除
(4) データもインポート(スキーマ作成に失敗してもインポートを続ける)

(1) スキーマのみインポート(インデックスも作成しない)
下記のようなコマンドでインポートします。オプションとしては"スキーマのみ"(rows=n)と
"インデックスは作成しない"(indexes=n)を指定しておきます。

$ imp suzlab/suzlab123@suzlab.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com:1521/suzlab \
> indexes=n \
> rows=n \
> full=y \
> file=suzlab.dmp

(2) 全てのテーブルの文字型の列のサイズをバイト単位から文字数単位に再定義
下記PL/SQLを実行します。ポイントはALTER文で文字型の列を再定義するするときに、
例えば、サイズ"10"を指定するところを"10 CHAR"と明示的に文字単位になるように
指定します。

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;
/

(3) 統計情報のロックを解除
下記PL/SQLを実行します。この状態だと統計情報がロックされており、
統計情報をインポートするときにエラーになってしまいます。
(詳しくはコチラで紹介しています)

SET LINESIZE 2000;
SET SERVEROUTPUT ON;
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;
/

(4) データもインポート(スキーマ作成に失敗してもインポートを続ける)
再度、下記のようなコマンドでインポートします。オプションとしては
"スキーマ作成にエラーがあってもデータインポートを続行"(ignore=y)を指定します。

$ imp suzlab/suzlab123@suzlab.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com:1521/suzlab \
> ignore=y
> full=y
> file=suzlab.dmp

この手順で、どんどん"Oracle on EC2"を"Oracle RDS"に移行していこう...
--------
http://www.suz-lab.com

0 コメント: