Oracle RDSの文字コードはAL32UTF8で固定されています。
そのため、他の文字コードのOracleデータベースのデータを移行(exp/imp)する際に、インポート(imp)時、下記のようなエラーが発生することがあります。
ORA-12899: 列"SUZLAB"."SUZLAB_TABLE"."SUZ_LAB_COLUMN"の値が大きすぎます(実際: 150、最大: 100)
このエラーは、Oracleの文字型の列のサイズ指定が、デフォルトではバイト単位で指定されるので、同じ一文字でも日本語などでは、例えば元は2バイトでも移行した際、AL32UTF8に変換されて、3バイトになってしまい、結果として列に指定されたサイズ(バイト単位)を超えてしまうことが原因です。
ただOracle(Oracle RDS)には、Oracle RDSでnls_length_semanticsの値をCHARに(文字型のサイズをバイト単位から文字数単位に)で紹介したとおり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を実行します。
この状態だと統計情報がロックされているため、
統計情報をインポートするときにエラーになってしまいます。
(詳しくはOracleで統計情報のインポート時にエラー(ORA-20005)になったらで紹介しています)
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に移行していきます。