DataPump / include、exclude を同時に使う
DataPump の CLI である expdp / impdp コマンドでは include と exclude オプションは排他関係のあるオプションで両方を同時に設定することができない。
もし、制約 (CONSTRAINT) のうち 参照整合性制約 (REF_CONSTRAINT) を除いた制約だけを移行したい場合
エクスポート側で CONSTRAINT を INCLUDE しているダンプを出力しインポート側で REF_CONSTRAINT を EXCLUDE するという2段階のフィルタリングを行なう必要がある。
expdp (impdp) で include と exclude を同時に指定した場合、パラメータの組み合わせが無効という UDE-00011 のエラーが発生する
$ expdp ... schemas="RIVUS" include="CONSTRAINT" exclude="REF_CONSTRAINT"
...
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
UDE-00011: parameter include is incompatible with parameter exclude
DBMS_DATAPUMP パッケージ (DataPump の PL/SQL インターフェイス )
DBMS_DATAPUMP は DataPump を PL/SQL から使用するための API であり、exclude と include の同時指定が可能。
テキストタイプのパラメータの設定値はほぼ同じであるが 'YES' / 'NO' にて指定するタイプのパラメータは YES => 1 / NO => 0 と数値に変換して設定する。
スキーマモードのときの階層の一部
TABLE の下位階層パス(内包要素)には TRIIGER, CONSTRAINT などがあり、CONSTRAINT の下位には REF_CONSTRAINT がある。
| | SIMPLE_PATH | OBJECT_PATH | 対象 |
TABLE | TABLE | 表 |
SCHEMA_EXPORT/TABLE |
| TRIGGER | TRIGGER | トリガー |
TABLE/TRIGGER |
SCHEMA_EXPORT/TABLE/TRIGGER | |
CONSTRAINT | CONSTRAINT | 制約 (参照制約を含む) |
TABLE/CONSTRAINT |
SCHEMA_EXPORT/TABLE/CONSTRAINT |
| REF_CONSTRAINT | REF_CONSTRAINT | 参照制約 |
CONSTRAINT/REF_CONSTRAINT |
TABLE/CONSTRAINT/REF_CONSTRAINT |
SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT |
METADATA_FILTER / exclude include 同時指定
スキーマモードで "RIVUS" ユーザー の 「表定義、表データ、内包関連オブジェクト」 をエクスポートするが 「トリガー」、「参照整合性制約」、「統計情報」を除いたダンプファイルを作成する PL/SQL スクリプト。
set serveroutput on
declare
type t_key_value is table of varchar2(4000) index by varchar2(128);
v_filter t_key_value;
v_key varchar2(128);
--
v_dp_handle number;
v_username varchar2(4000);
v_dir varchar2(4000);
v_dumpfile varchar2(4000);
v_logfile varchar2(4000);
v_jobname varchar2(4000);
v_stat varchar2(4000);
v_mesg varchar2(4000);
begin
-- <CONFIG>
-- ジョブ名、ダンプファイル、ログファイル設定
v_username := USER;
v_dir := 'DATA_PUMP_DIR';
v_dumpfile := 'MY_EXPORT.DMP';
v_logfile := 'MY_EXPORT.LOG';
v_jobname := 'MY_EXPORT_JOB';
--
-- METRADATA_FILTER モード、対象スキーマ名設定
v_filter('SCHEMA_EXPR') := q'{in ('RIVUS')}';
--
-- METADATA_FILTER INCLUDE の設定
v_filter('INCLUDE_PATH_EXPR') := q'{in ('TABLE')}';
--
-- METADATA_FILTER EXCLUDE の設定
v_filter('EXCLUDE_PATH_EXPR') := q'{in ('REF_CONSTRAINT','TRIGGER','STATISTICS')}';
-- </CONFIG>
--
-- Create JOB
v_dp_handle := dbms_datapump.open (
operation => 'EXPORT', -- 「EXPORT」 / IMPORT
job_mode => 'SCHEMA', -- 全体 / 「スキーマ」 / 表 / 表領域 /
job_name => v_jobname
);
-- Dumpfile(s)
dbms_datapump.add_file (
handle => v_dp_handle,
filename => v_dumpfile,
directory => v_dir,
filetype => dbms_datapump.ku$_file_type_dump_file,
reusefile => 1
);
-- Logfile
dbms_datapump.add_file (
handle => v_dp_handle,
filename => v_logfile,
directory => v_dir,
filetype => dbms_datapump.ku$_file_type_log_file,
reusefile => 1
);
-- METADATA_FILTER(s)
v_key := v_filter.first;
while (v_key is not null) loop
dbms_datapump.metadata_filter (
handle => v_dp_handle,
name => v_key,
value => v_filter(v_key)
);
v_key := v_filter.next(v_key);
end loop;
-- SET PARAMETER
dbms_datapump.set_parameter (
handle => v_dp_handle,
name => 'METRICS',
value => 1
);
--
-- <START JOB>
v_mesg := '[' || to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') || '] Job "' || v_username || '.' || v_jobname || '" start ';
dbms_output.put_line (v_mesg);
dbms_datapump.log_entry (handle => v_dp_handle, message => v_mesg);
dbms_datapump.start_job(handle => v_dp_handle);
dbms_datapump.wait_for_job(handle => v_dp_handle, job_state => v_stat);
dbms_datapump.detach(handle => v_dp_handle);
dbms_output.put_line ('[' || to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') || '] ' || initcap(v_stat));
-- </START JOB>
end;
/