share facebook facebook twitter menu hatena pocket slack

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

鈴木 宏康

WRITTEN BY 鈴木 宏康

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に移行していきます。

こちらの記事はなかの人(suz-lab)監修のもと掲載しています。
元記事は、こちら

鈴木 宏康

鈴木 宏康

愛知県生まれ。東京工業大学大学院修士課程修了。在学時より、ベンチャー企業でインターネットに関する業務に携わり、現在はクラウド(主にAmazon Web Services)上での開発・運用を軸とした事業の、業務の中心として活躍。

cloudpack

cloudpackは、Amazon EC2やAmazon S3をはじめとするAWSの各種プロダクトを利用する際の、導入・設計から運用保守を含んだフルマネージドのサービスを提供し、バックアップや24時間365日の監視/障害対応、技術的な問い合わせに対するサポートなどを行っております。
AWS上のインフラ構築およびAWSを活用したシステム開発など、案件のご相談はcloudpack.jpよりご連絡ください。