RDS Oracle 雑多なメモ#21 / DBMS_DATAPUMPパッケージ Schema mode de expdp/impdp - metadata onlyとデータのインポート
Previously, Mac De Oracle...
RDS Oracle 雑多なメモ#20 / DBMS_DATAPUMPパッケージ Schema mode de expdp/impdpの準備 SQL_FILEモードでDDL抜き出し
でした。
今回は、RDS Oracleへスキーマ単位のインポートでメタデータだけをインポートするにはどうするかというおはなし。
メタデータだけをインポートするにはこれまでに何度か話題にしたエクスポートされたオブジェクトとその階層を表すオブジェクトパスが重要な意味を持っています。
まずはフルエクスポートログにリストされるオブジェクトパスの例をご覧ください。
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE_BODIES/PACKAGE/PACKAGE_BODY
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type DATABASE_EXPORT/STATISTICS/MARKER
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/ROLE
Processing object type DATABASE_EXPORT/RADM_FPTM
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/ON_USER_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
Processing object type DATABASE_EXPORT/RESOURCE_COST
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/WITH_GRANT_OPTION/OBJECT_GRANT
Processing object type DATABASE_EXPORT/CONTEXT
Processing object type DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE
上記オブジェクトパスツリーを一部省略しつつ見やすくすると以下のようなツリー構造になっているのが理解しやすいはず。
図にも書いていますが、メタデータのみをインポートするにはデータを除外(Exclude)してしまえばよいわけです。
DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
また、同じツリーの階層にあるオブジェクトもスキーマレベルのインポートではインポートされないということも理解しやすいはずです。：）
なお、DATAPUMPが扱うオブジェクトパスの詳細は
データベース・リファレンス - 6.238 SCHEMA_EXPORT_OBJECTS
を参照してくださいね。知らない方は多いですが、DATAPUMPと仲良くなるためには必要なビューです。
では、スキーマモードのメタデータオンリーインポートの仕込みから
同じデータベースへスキーマをインポートするので事前に削除しておきます。
BILL> drop user hoge cascade;
User dropped.
BILL> drop profile fizzbuzz;
Profile dropped.
BILL> drop role fizzbuzz;
Role dropped.
BILL> drop tablespace fizzbuzz including contents and datafiles;
Tablespace dropped.
たとえばこの状態でスキーマレベルのインポートを行ってしまうと、もう、想像はできていると思いますが、依存オブジェクト不足によりインポートは失敗します。
ユーザーのデフォルト表領域や依存するプロファイルが存在しない状態ではインポート時に実行されるCREATE USER文がエラーとなりインポートはエラーで終了することになります。
BILL> @imp_schema_metadataonly data_pump_dir fullexp hoge
Master table "BILL"."SYS_IMPORT_SCHEMA_03" successfully loaded/unloaded
Starting "BILL"."SYS_IMPORT_SCHEMA_03":
Processing object type DATABASE_EXPORT/SCHEMA/USER
ORA-39083: Object type USER:"HOGE" failed to create with error:
ORA-02380: profile FIZZBUZZ does not exist
Failing sql is:
CREATE USER "HOGE" IDENTIFIED BY VALUES
...中略...
TABLESPACE "FIZZBUZZ" TEMPORARY TABLESPACE "TEMP" PROFILE "FIZZBUZZ"
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
ORA-39083: Object type ROLE_GRANT failed to create with error:
ORA-01917: user or role 'HOGE' does not exist
Failing sql is:
GRANT "CONNECT" TO "HOGE"
ORA-39083: Object type ROLE_GRANT failed to create with error:
ORA-01924: role 'FIZZBUZZ' not granted or does not exist
Failing sql is:
GRANT "FIZZBUZZ" TO "HOGE"
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
ORA-39083: Object type DEFAULT_ROLE:"HOGE" failed to create with error:
ORA-01918: user 'HOGE' does not exist
...中略...
Job "BILL"."SYS_IMPORT_SCHEMA_03" completed with 7 error(s) at Fri Jul 24 17:51:45 2020 elapsed 0 00:00:03
PL/SQL procedure successfully completed.
では、上記のようなエラーを回避するため、前回フルエクスポートから取り出したDDL を実行して作成します。
BILL> CREATE BIGFILE TABLESPACE "FIZZBUZZ" DATAFILE
2 SIZE 104857600
3 AUTOEXTEND ON NEXT 104857600 MAXSIZE 1073741824
4 LOGGING ONLINE PERMANENT BLOCKSIZE 8192
5 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
6 NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
Tablespace created.
BILL> CREATE ROLE "FIZZBUZZ";
Role created.
BILL> CREATE PROFILE "FIZZBUZZ"
2 LIMIT
3 COMPOSITE_LIMIT DEFAULT
4 SESSIONS_PER_USER DEFAULT
5 CPU_PER_SESSION DEFAULT
6 CPU_PER_CALL DEFAULT
7 LOGICAL_READS_PER_SESSION DEFAULT
8 LOGICAL_READS_PER_CALL DEFAULT
9 IDLE_TIME UNLIMITED
10 CONNECT_TIME DEFAULT
11 PRIVATE_SGA DEFAULT
12 FAILED_LOGIN_ATTEMPTS DEFAULT
13 PASSWORD_LIFE_TIME DEFAULT
14 PASSWORD_REUSE_TIME DEFAULT
15 PASSWORD_REUSE_MAX DEFAULT
16 PASSWORD_VERIFY_FUNCTION DEFAULT
17 PASSWORD_LOCK_TIME DEFAULT
18 PASSWORD_GRACE_TIME DEFAULT
19 INACTIVE_ACCOUNT_TIME DEFAULT ;
Profile created.
準備ができたので、メタデータオンリーのスキーマモードインポートを試してみます。
スキーマ以下のオブジェクトはインポート（統計情報をフィルタするのを忘れましたがw）できました。hoge表には1行だけデータがありますがデータはインポートされていないことが確認できるはずです。
BILL> @imp_schema_metadataonly data_pump_dir fullexp hoge
Master table "BILL"."SYS_IMPORT_SCHEMA_04" successfully loaded/unloaded
Starting "BILL"."SYS_IMPORT_SCHEMA_04":
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type DATABASE_EXPORT/STATISTICS/MARKER
Job "BILL"."SYS_IMPORT_SCHEMA_04" successfully completed at Fri Jul 24 17:56:34 2020 elapsed 0 00:00:13
PL/SQL procedure successfully completed.
BILL> select username from dba_users where username='HOGE';
USERNAME
------------------------------
HOGE
BILL> select count(1) from hoge.hoge;
COUNT(1)
----------
0
では次にデータだけをインポートしてみます。
TABLE_DATAのみをインポートする方法と、スキーマインポートで表が存在する場合に、データをTRUNCATE後、データインポートする方法があります。（表が存在している場合のデフォルトの動作はデータインポートのスキップです）
以下の例では 通常 のスキーマインポートで表が存在する場合 、既存データをTRUNCATEして データをインポートする方法で行ってます。ユーザーが存在する場合にはCREATE USERは失敗し表が存在した場合にtruncate後にデータがインポートされます
BILL> @imp_schema data_pump_dir fullexp hoge
Master table "BILL"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "BILL"."SYS_IMPORT_SCHEMA_01":
Processing object type DATABASE_EXPORT/SCHEMA/USER
ORA-31684: Object type USER:"HOGE" already exists
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Table "HOGE"."HOGE" exists and has been truncated.
Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
. . imported "HOGE"."HOGE" 5.046 KB 1 rows
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type DATABASE_EXPORT/STATISTICS/MARKER
Job "BILL"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at Fri Jul 24 18:01:30 2020 elapsed 0 00:00:05
PL/SQL procedure successfully completed.
BILL> select count(1) from hoge.hoge;
COUNT(1)
----------
1
スクリプトの例は以下のとおり
DBMS＿DATAPUMP．METADATA＿FILTER()プロシージャでデータを示すオブジェクトパス（DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA）を除外しているところがポイント
BILL> !cat imp_schema_metadataonly.sql
SET VERIFY OFF
SET SERVEROUTPUT ON
DECLARE
v4Debug VARCHAR2(200);
cDirectory CONSTANT VARCHAR2(30) := UPPER('&1');
cDumpFileName CONSTANT VARCHAR2(64) := '&2'||'.dmp';
cLogFileName CONSTANT VARCHAR2(64) := '&2'||'.log';
cSchemaName CONSTANT VARCHAR2(30) := UPPER('&3');
cExcludePath CONSTANT VARCHAR2(128) := 'DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA';
i NUMBER;
vDataPumpJobHandle NUMBER;
vProgress_ratio NUMBER;
vJobState VARCHAR2(30);
oLogEntry ku$_LogEntry;
oStatus ku$_Status;
BEGIN
DBMS_OUTPUT.ENABLE;
v4Debug := 'OPEN';
vDataPumpJobHandle
:= DBMS_DATAPUMP.OPEN (
operation => 'IMPORT'
,job_mode => 'SCHEMA'
,job_name => NULL
,remote_link => NULL
);
v4Debug := 'ADD_FILE - dumpfile';
DBMS_DATAPUMP.ADD_FILE (
handle => vDataPumpJobHandle
,filename => cDumpFileName
,directory => cDirectory
,filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE
);
v4Debug := 'ADD_FILE - logfile';
DBMS_DATAPUMP.ADD_FILE (
handle => vDataPumpJobHandle
,filename => cLogFileName
,directory => cDirectory
,filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE
);
v4Debug := 'METADATA_FILTER - schema name';
DBMS_DATAPUMP.METADATA_FILTER (
handle => vDataPumpJobHandle
,name => 'SCHEMA_LIST'
,value => '''' || cSchemaName || ''''
);
v4Debug := 'EXCLUDE_PATH='||cExcludePath;
DBMS_DATAPUMP.METADATA_FILTER (
handle => vDataPumpJobHandle
,name => 'EXCLUDE_PATH_LIST'
,value => '''' || cExcludePath || ''''
);
v4Debug := 'START_JOB';
DBMS_DATAPUMP.START_JOB (
handle => vDataPumpJobHandle
);
v4Debug := 'JOB_STATE';
vProgress_ratio := 0;
vJobState := 'UNDEFINED';
WHILE (vJobState != 'COMPLETED') AND (vJobState != 'STOPPED') LOOP
DBMS_DATAPUMP.GET_STATUS (
vDataPumpJobHandle
,DBMS_DATAPUMP.KU$_STATUS_JOB_ERROR
+ DBMS_DATAPUMP.KU$_STATUS_JOB_STATUS
+ DBMS_DATAPUMP.KU$_STATUS_WIP
,-1
,vJobState
,oStatus
);
IF (BITAND(oStatus.mask, DBMS_DATAPUMP.KU$_STATUS_WIP) != 0)
THEN
oLogEntry := oStatus.wip;
ELSE
IF (BITAND(oStatus.mask, DBMS_DATAPUMP.KU$_STATUS_JOB_ERROR) != 0)
THEN
oLogEntry := oStatus.error;
ELSE
oLogEntry := NULL;
END IF;
END IF;
IF oLogEntry IS NOT NULL
THEN
i := oLogEntry.FIRST;
WHILE i IS NOT NULL LOOP
DBMS_OUTPUT.PUT_LINE(oLogEntry(i).LogText);
i := oLogEntry.NEXT(i);
END LOOP;
END IF;
END LOOP;
DBMS_DATAPUMP.DETACH(vDataPumpJobHandle);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(sqlerrm());
DBMS_OUTPUT.PUT_LINE(v4Debug);
RAISE;
END;
/
UNDEFINE 1
UNDEFINE 2
UNDEFINE 3
SET SERVEROUTPUT OFF
SET VERIFY ON
DBMS_DATAPUMP.SET_PARAMETER()でTABLE_EXISTS_ACTION (表が存在する場合)でTRUNCATE（既存データのトランケート後データをインポート)する支持をしています。
BILL> !cat imp_schema.sql
SET VERIFY OFF
SET SERVEROUTPUT ON
DECLARE
v4Debug VARCHAR2(200);
cDirectory CONSTANT VARCHAR2(30) := UPPER('&1');
cDumpFileName CONSTANT VARCHAR2(64) := '&2'||'.dmp';
cLogFileName CONSTANT VARCHAR2(64) := '&2'||'.log';
cSchemaName CONSTANT VARCHAR2(30) := UPPER('&3');
i NUMBER;
vDataPumpJobHandle NUMBER;
vProgress_ratio NUMBER;
vJobState VARCHAR2(30);
oLogEntry ku$_LogEntry;
oStatus ku$_Status;
BEGIN
DBMS_OUTPUT.ENABLE;
v4Debug := 'OPEN';
vDataPumpJobHandle
:= DBMS_DATAPUMP.OPEN (
operation => 'IMPORT'
,job_mode => 'SCHEMA'
,remote_link => NULL
);
v4Debug := 'ADD_FILE - dumpfile';
DBMS_DATAPUMP.ADD_FILE (
handle => vDataPumpJobHandle
,filename => cDumpFileName
,directory => cDirectory
,filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE
);
v4Debug := 'ADD_FILE - logfile';
DBMS_DATAPUMP.ADD_FILE (
handle => vDataPumpJobHandle
,filename => cLogFileName
,directory => cDirectory
,filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE
);
v4Debug := 'METADATA_FILTER - schema name';
DBMS_DATAPUMP.METADATA_FILTER (
handle => vDataPumpJobHandle
,name => 'SCHEMA_LIST'
,value => '''' || cSchemaName || ''''
);
v4Debug := 'TABLE_EXISTS_ACTION=TRUNCATE';
DBMS_DATAPUMP.SET_PARAMETER (
handle => vDataPumpJobHandle
,name => 'TABLE_EXISTS_ACTION'
,value => 'TRUNCATE'
);
v4Debug := 'START_JOB';
DBMS_DATAPUMP.START_JOB (
handle => vDataPumpJobHandle
);
v4Debug := 'JOB_STATE';
vProgress_ratio := 0;
vJobState := 'UNDEFINED';
WHILE (vJobState != 'COMPLETED') AND (vJobState != 'STOPPED') LOOP
DBMS_DATAPUMP.GET_STATUS (
vDataPumpJobHandle
,DBMS_DATAPUMP.KU$_STATUS_JOB_ERROR
+ DBMS_DATAPUMP.KU$_STATUS_JOB_STATUS
+ DBMS_DATAPUMP.KU$_STATUS_WIP
,-1
,vJobState
,oStatus
);
IF (BITAND(oStatus.mask, DBMS_DATAPUMP.KU$_STATUS_WIP) != 0)
THEN
oLogEntry := oStatus.wip;
ELSE
IF (BITAND(oStatus.mask, DBMS_DATAPUMP.KU$_STATUS_JOB_ERROR) != 0)
THEN
oLogEntry := oStatus.error;
ELSE
oLogEntry := NULL;
END IF;
END IF;
IF oLogEntry IS NOT NULL
THEN
i := oLogEntry.FIRST;
WHILE i IS NOT NULL LOOP
DBMS_OUTPUT.PUT_LINE(oLogEntry(i).LogText);
i := oLogEntry.NEXT(i);
END LOOP;
END IF;
END LOOP;
DBMS_DATAPUMP.DETACH(vDataPumpJobHandle);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(sqlerrm());
DBMS_OUTPUT.PUT_LINE(v4Debug);
RAISE;
END;
/
UNDEFINE 1
UNDEFINE 2
UNDEFINE 3
SET SERVEROUTPUT OFF
SET VERIFY ON
今回は、上記２つのスクリプトに分けましたが、１つにまとめるように作り変えればより便利できますよ！ それはみなさんへの宿題w
ということで今回のネタはおしまい :)
・Data Pumpも癖モノだよね〜w その１ - queryパラメーターの解析タイミング・Data Pumpも癖モノだよね〜w その２ - Materialized ViewをTableとして移行する・Data Pumpも癖モノだよね〜w その3 - dbms_job と dbms_scheduler との複雑な関係・Data Pumpも癖モノだよね〜w その4 - schemaモードでMviewを他のPDBへ複製・Data Pumpも癖モノだよね〜w その4と1/2 - schemaモードでMviewを他のPDBへ複製 (紛らわしいステータスw）
・Data Pumpも癖モノだよね〜w その5 - schemaモードでMviewを他のPDBへ複製（オプジェクトパス de 絞り込み）
・Data Pumpも癖モノだよね〜w その6 - schemaモードでMviewを他のPDBへ複製（オプジェクトパスが不足すると...
・RDS Oracle 雑多なメモ#1 / FAQ
・RDS Oracle 雑多なメモ#2 / FAQ
・RDS Oracle 雑多なメモ#3 / FAQ
・RDS Oracle 雑多なメモ#4 / FAQ
・RDS Oracle 雑多なメモ#5 / FAQ
・RDS Oracle 雑多なメモ#6 / FAQ
・RDS Oracle 雑多なメモ#7 / FAQ
・RDS Oracle 雑多なメモ#8 / FAQ
・RDS Oracle 雑多なメモ#9 / FAQ
・RDS Oracle 雑多なメモ#10 / FAQ
・RDS Oracle 雑多なメモ#11 / FAQ
・RDS Oracle 雑多なメモ#12 / FAQ
・RDS Oracle 雑多なメモ#13 / FAQ
・RDS Oracle 雑多なメモ#14 - おまけ / FAQ
・RDS Oracle 雑多なメモ#15 - おまけのおまけ / FAQ
・RDS Oracle 雑多なメモ#16 - 再び:) / FAQ
・RDS Oracle 雑多なメモ#17/ FAQ
・RDS Oracle 雑多なメモ#18 / DBMS_DATAPUMPパッケージ de expdp/impdp
・RDS Oracle 雑多なメモ#19 FAQ / DBMS_DATAPUMPパッケージ de ジョブの停止
・RDS Oracle 雑多なメモ#20 / DBMS_DATAPUMPパッケージ Schema mode de expdp/impdpの準備 SQL_FILEモードでDDL抜き出し
最近のコメント