oracleのデータダンプのインポートエクスポートを行うexpdp、impdpコマンドでの特定テーブルの除外方法についてのメモ。
インポートエクスポートの中の除外設定の特にテーブルについてのみ。
前提と基本
expdpコマンドでスキーマのFULLエクスポートを行い、そこからimpdpコマンドで特定のテーブルを除外してインポートを行います。
エクスポート時に除外する場合でも同様の記述で実行できると思います。
・expdpコマンド
参考までに検証に使用するためのダンプをエクスポートするコマンドは以下。
expdp wantan/wantan schemas=wantan directory=DUMPDIR dumpfile=TEST_DMP.dmp
・impdpコマンド
基本とするインポートコマンド。
impdp wantan/wantan remap_schema=wantan:test_1 directory=DUMPDIR dumpfile=TEST_DMP.dmp
・テーブル一覧
データダンプに含まれるテーブルを以下の通りです。
フルインポートを実行すると全てのテーブルがインポートされます。
| TABLE_NAME |
|---|
| ATTACHMENT |
| CUSTOMER |
| DEPARTMENT |
| EMP |
| PRODUCT |
| SALE |
| SALE_DETAIL |
| SYS_EXPORT_SCHEMA_01 |
| SYS_EXPORT_SCHEMA_02 |
後半の内容についてはこんな方法もあるよ程度で参考までに。もっとスマートな方法があるかもしれません。
特定テーブル除外
除外にはexclude句を使用します。
今回はテーブル除外のみを扱いますがINDEXなどの単位でも除外指定できます。
まず特定のテーブル(SYS_EXPORT_SCHEMA_01)を除外する場合には以下のように指定します。
最も基本的な指定方法です。
基本的に記号をエスケープすると思えば大丈夫だと思います。
EXCLUDE=TABLE:\"IN \(\'SYS_EXPORT_SCHEMA_01\'\)\"
・impdpコマンド
impdp wantan/wantan remap_schema=wantan:test_2 directory=DUMPDIR dumpfile=TEST_DMP.dmp EXCLUDE=TABLE:\"IN \(\'SYS_EXPORT_SCHEMA_01\'\)\"
複数テーブルを指定したい場合には以下のような指定もできます。
EXCLUDE=TABLE:\"IN \(\'SYS_EXPORT_SCHEMA_01\'\,\'SYS_EXPORT_SCHEMA_02\'\)\"
除外テーブルのLIKE指定
除外テーブルはLIKEでも指定することができます。
EXCLUDE=TABLE:\"LIKE \'SYS_%\'\"
・impdpコマンド
impdp wantan/wantan remap_schema=wantan:test_3 directory=DUMPDIR dumpfile=TEST_DMP.dmp EXCLUDE=TABLE:\"LIKE \'SYS_%\'\"
これらのことから、指定方法は、ようはSQLのwhere句と同じような指定ができると思っておけば良さそうです。
EXCLUDE=TABLE:を指定したときのイメージは以下のような感じでしょうか。
(WHERE TABLE_NAME) IN ('SYS_EXPORT_SCHEMA_01')
(WHERE TABLE_NAME) LIKE 'SYS_%'
複数条件の指定
複数テーブルの除外を行うときに異なる条件を指定したいとき。
EXCLUDE=TABLE:\"IN \(\'SALE\'\)\" EXCLUDE=TABLE:\"LIKE \'SYS_%\'\"
・impdpコマンド
impdp wantan/wantan remap_schema=wantan:test_3 directory=DUMPDIR dumpfile=TEST_DMP.dmp EXCLUDE=TABLE:\"IN \(\'SALE\'\)\" EXCLUDE=TABLE:\"LIKE \'SYS_%\'\"
個人的にはあんまりセンスを感じない書き方にも見えるけどこれで実行可能。
除外テーブルをテーブルデータ管理する
除外の指定にはIN句の中でSQLを使用することもできるので、除外テーブルを直接指定せずに管理スキーマを別に作成して、そこに除外テーブル一覧みたいなテーブルを作成することでも除外指定をすることができます。
例えば、EXCLUDE_TABLEというスキーマを作成してそこにEXCLUDE_TABLE1というテーブルを作成する。
テーブルの内容は以下のような感じで最低限テーブル名のカラムだけ持たせておけばよい。
・EXCLUDE_TABLE1
| TABLE_NAME |
|---|
| SALE |
| SYS_EXPORT_SCHEMA_02 |
| SYS_EXPORT_SCHEMA_01 |
上記のような状況で以下のように指定する。
テーブル名の指定ではなくSQLなので、シングルクオーテーションは不要になる。
EXCLUDE=TABLE:\"IN \(SELECT TABLE_NAME FROM EXCLUDE_TABLE.EXCLUDE_TABLE1\)\"
・impdpコマンド
impdp wantan/wantan remap_schema=wantan:test_2 directory=DUMPDIR dumpfile=TEST_DMP.dmp EXCLUDE=TABLE:\"IN \(SELECT TABLE_NAME FROM EXCLUDE_TABLE.EXCLUDE_TABLE1\)\"
または、SQLが普通に発行できるということが分かれば以下のような管理方法も考えられる。
とりあえず全テーブルのデータをぶち込んでフラグで除外を管理する。
・EXCLUDE_TABLE2
| TABLE_NAME | FLAG |
|---|---|
| ATTACHMENT | 0 |
| CUSTOMER | 0 |
| DEPARTMENT | 0 |
| EMP | 0 |
| PRODUCT | 0 |
| SALE | 1 |
| SALE_DETAIL | 1 |
| SYS_EXPORT_SCHEMA_01 | 1 |
| SYS_EXPORT_SCHEMA_02 | 1 |
EXCLUDE=TABLE:\"IN \(SELECT TABLE_NAME FROM EXCLUDE_TABLE.EXCLUDE_TABLE2 WHERE FLAG=\'1\'\)\"
・impdpコマンド
impdp wantan/wantan remap_schema=wantan:test_2 directory=DUMPDIR dumpfile=TEST_DMP.dmp EXCLUDE=TABLE:\"IN \(SELECT TABLE_NAME FROM EXCLUDE_TABLE.EXCLUDE_TABLE2 WHERE FLAG=\'1\'\)\"
ここまでやると、結構カチカチに管理できる気もしますが仕組みの構築や管理が逆に大変になってくる気もするので、環境や対象テーブルなど次第でしょうか。
データは除外したいけどテーブルは作成したい
上記のテーブル除外では、テーブルのインポートから除外するため当然ながらテーブル自体が作成されません。
そして、テーブルは作成したいけどデータだけ除外するという方法がいまいちうまく見つからなかった。
変にEXCLUDEに拘らず、FULLインポートしてからデータをDELETEするとか、EXCLUDEしてから必要なものだけCREATE TABLEするとかでもいいのかもしれません。
以下のような方法でお茶を濁す。二度impdpを実行するのはなんとなくナンセンス感があってもやっとしている。
SALE~はデータはインポートしたくないがテーブルは作成したい、SYS_EXPORT~はデータもインポートしたくないしテーブルも作成したくないような状況を想定する。
・メタ情報のみを先にインポートする。
まず、テーブル自体も作成したくないテーブルを除外してMETADATA_ONLYでインポートする。
impdp wantan/wantan remap_schema=wantan:test_3 directory=DUMPDIR dumpfile=TEST_DMP.dmp CONTENT=METADATA_ONLY TABLE_EXISTS_ACTION=REPLACE EXCLUDE=TABLE:\"LIKE \'SYS_%\'\"
・データのみを後からインポートする。
次にデータをインポートしたくない全てのテーブルを除外してDATA_ONLYでインポートする。
impdp wantan/wantan remap_schema=wantan:test_3 directory=DUMPDIR dumpfile=TEST_DMP.dmp CONTENT=DATA_ONLY EXCLUDE=TABLE:\"IN \(SELECT TABLE_NAME FROM EXCLUDE_TABLE.EXCLUDE_TABLE2 WHERE FLAG=\'1\'\)\"
これで一応要件は達成できる。
ちょっと気になるのが、METADATA_ONLYでテーブルを作成した後にDATA_ONLYでデータを投入すると実行速度がDATA_ONLYを指定しないときに比べて遅い気がする。
二度目の実行はDATA_ONLYを指定せずにTABLE_EXISTS_ACTION=REPLACEを指定して置き換え直す方法でもいいかもしれない。
【参考】
【Oracle】SQL集 - Qiita
DataPump / Export、Import （オプション） - オラクル・Oracleをマスターするための基本と仕組み
DataPump / include、exclude フィルタ - オラクル・Oracleをマスターするための基本と仕組み
