« 2020年2月 | トップページ

2020年7月26日 (日)

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

上記オブジェクトパスツリーを一部省略しつつ見やすくすると以下のようなツリー構造になっているのが理解しやすいはず。

20200726-10936

図にも書いていますが、メタデータのみをインポートするにはデータを除外(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


今回は、上記2つのスクリプトに分けましたが、1つにまとめるように作り変えればより便利できますよ! それはみなさんへの宿題w 

ということで今回のネタはおしまい :)




Data Pumpも癖モノだよね〜w その1 - queryパラメーターの解析タイミング
Data Pumpも癖モノだよね〜w その2 - 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抜き出し

| | コメント (0)

2020年7月25日 (土)

RDS Oracle 雑多なメモ#20 / DBMS_DATAPUMPパッケージ Schema mode de expdp/impdpの準備 SQL_FILEモードでDDL抜き出し

RDS Oracle 雑多なメモ#20 / DBMS_DATAPUMPパッケージ Schema mode de expdp/impdpの準備


Previously, Mac De Oracle...

RDS Oracle 雑多なメモ#19 FAQ / DBMS_DATAPUMPパッケージ de ジョブの停止

でした。

今回は、RDS Oracleへスキーマ単位でインポートを行う準備に必要などを。

実は、RDS Oracleでは、フルインポートはしないようにと記載されています。ということは、つまりスキーマモードでのインポートと、それ以下の単位でのインポートのみを行うこと、となっています。ここがポイント

詳細は以下マニュアルを参照ください。
Amazon RDS での Oracle へのデータのインポート


フルモードでインポートできない場合の注意点として、スキーマレベルでインポートする際に必要となる可能性のあるオブジェクトは事前に何がしかの方法で作成しておく必要があるということを意味します。
代表的なオブジェクトは以下、

  • ユーザーのデフォルトTABLESPACE(USERS表をそのまま利用しているケースは希だと思います)
  • ユーザーのPROFILE(パスワード有効期限管理などで利用しているケースは多いかも)
  • ROLE(直接付与権限以外はロールを付与して権限を管理することは多いはずです)
  • DIRECTORY(Oracle側で事前に作成しているディレクトリ以外のディレクトリへのアクセスが必要な場合にはディレクトリオブジェクトも事前に作成しておく必要があります)

さらに、スキーマ間で依存している場合、特に、他のスキーマのオブジェクト権限を付与する必要がある場合は、スキーマをインポートする順番にも気を遣う必要も出てきます。
インポート時にエラーが出ないものもありますが。。それは今回のテーマではないのでこの辺でw
(依存しているオブジェクトは事前にALL/DBA_DEPENDENCIESビューを利用して調査しておくとよいでしょうね)



では、DDLを取得する前に、ネタを仕込んでおきましょう。

表領域 fizzbuzz をデフォルト表領域とし、プロファイル fizzbuzz が設定されたユーザー hoge を作成します。
また、connectロールとresourceロールを付与された  fizzbuzz ロールが hoge に付与されているとします。


表領域の追加

BILL> create tablespace fizzbuzz datafile size 100m autoextend on maxsize 1g;

Tablespace created.

プロファイルの追加

BILL> create profile fizzbuzz limit idle_time unlimited;

Profile created.

ロールの追加

BILL> create role fizzbuzz;

Role created.

BILL> grant connect to fizzbuzz;

Grant succeeded.

BILL> grant resource to fizzbuzz;

Grant succeeded.


ユーザーの作成(デフォルト表領域とプロファイルの指定)

BILL> create user hoge identified by xxxxxxxxxxxxxxxx
2 default tablespace fizzbuzz
3 temporary tablespace temp
4 quota unlimited on fizzbuzz
5 profile fizzbuzz;

User created.


ロールの付与

BILL> grant fizzbuzz to hoge;

Grant succeeded.


上記のようなユーザーだとするとスキーマ単位のインポート前に表領域の作成、プロファイルの作成、ロールの作成が必要になりますよね。
(スキーマレベルのインポートでは依存オブジェクトが自動的にインポートされるわけでは無いので)

また、元のデータベースでOracle Managed Fileを利用した表領域では無い場合にはOracle Managed Fileを利用した表領域として再作成してあげる必要あります。(ここも重要。元のデータベースもOracle Managed fileを利用していればそのまま利用できます)

ということで、DBMS_METADATA.GET_DDL()または、フルエクスポートしたData Pumpのダンプファイルファイルから上記に該当するDDLを抜き出し、事前にオブジェクトを作成しておく必要がありますよね。
(DDLソースコード管理されているのであれば、それらを再利用することも可能だとは思いますが、世の中広いのでそんな管理されていないところも多いでしょうし。DDLを取り出す複数の方法は覚えてて損は無いですよ :)


今回は、フルエクスポートダンプファイルがあるので、そこからData Pump APIを利用して取り出してみようと思います。(DBMS_METADATA.GET_DDL()の方が楽だとは思います。個人的にはw)

フルダンプからDDLを取り出す際でも、重要なのがオブジェクトパス(このパスの階層を意識していないと思わぬ失敗をするのは昔のエントリの通り)ですが、今回は比較的シンプルなので確認してみましょう。


事前にエクスポートのログから以下のパスを探し出しておきます。
(今回の例では、表領域とプロファイル、それにロールへのオブジェクトパスです。以下がそのオブジェクトパス)

Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/ROLE

余談ですが、同じ階層オブジェクトパスにスキーマオブジェクトのパス(DATABASE_EXPORT/SCHEMA)が存在しています。同じ階層なので、スキーマレベルのインポートではTABLESPACE/ROLE/PROFILEはインポートされることはありません。(この階層構造を理解できれば楽)
DATABASE_EXPORT/SCHEMA以下がインポートの対象になるので、メタデータフィルターでEXCLUDE または INCLUDEするか制御できることになります。TABLESPACE,PROFILEやROLEはSCHEMAと同レベルなのでそもそも対象外となりフィルタすることはできません。

Processing object type DATABASE_EXPORT/SCHEMA/

理屈がわかれば癖の強いDATAPUMPなんてw 

では、DDLを取り出してみましょう。

前提のオブジェクトを含むフルエクスポートダンプファイル fullexp.dmp は事前に取得済みであるとします。

BILL> @extract_ddl data_pump_dir fullexp DATABASE_EXPORT/TABLESPACE'',''DATABASE_EXPORT/PROFILE'',''DATABASE_EXPORT/ROLE
Master table "BILL"."SYS_SQL_FILE_FULL_17" successfully loaded/unloaded
Starting "BILL"."SYS_SQL_FILE_FULL_17":
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/ROLE
Job "BILL"."SYS_SQL_FILE_FULL_17" successfully completed at Sun Jul 19 19:12:00 2020 elapsed 0 00:00:02

PL/SQL procedure successfully completed.

取り出したDDLを確認します

BILL> @cat_file data_pump_dir fullexp.txt

TEXT
-----------------------------------------------------------------------------

...中略...

-- new object type path: DATABASE_EXPORT/TABLESPACE
...中略...
CREATE BIGFILE TABLESPACE "FIZZBUZZ" DATAFILE
SIZE 104857600
AUTOEXTEND ON NEXT 104857600 MAXSIZE 1073741824
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
-- new object type path: DATABASE_EXPORT/PROFILE
...中略...
CREATE PROFILE "FIZZBUZZ"
LIMIT
COMPOSITE_LIMIT DEFAULT
SESSIONS_PER_USER DEFAULT
CPU_PER_SESSION DEFAULT
CPU_PER_CALL DEFAULT
LOGICAL_READS_PER_SESSION DEFAULT
LOGICAL_READS_PER_CALL DEFAULT
IDLE_TIME UNLIMITED
CONNECT_TIME DEFAULT
PRIVATE_SGA DEFAULT
FAILED_LOGIN_ATTEMPTS DEFAULT
PASSWORD_LIFE_TIME DEFAULT
PASSWORD_REUSE_TIME DEFAULT
PASSWORD_REUSE_MAX DEFAULT
PASSWORD_VERIFY_FUNCTION DEFAULT
PASSWORD_LOCK_TIME DEFAULT
PASSWORD_GRACE_TIME DEFAULT
INACTIVE_ACCOUNT_TIME DEFAULT ;
-- new object type path: DATABASE_EXPORT/ROLE
...中略...
CREATE ROLE "FIZZBUZZ";
...中略...


スキーマ hogeインポート前に作成が必要なオブジェクトのDDLを取得できました。


これでスキーマレベルのインポート準備完了。
つづく。

参考)
DDLをダンプファイルから取り出すスクリプト例は以下の通り。DBMS_DATAPUMP.OPEN()関数でoperationに’SQL_FILE'を渡しているところとDBMS_DATAPUMP.METADATA_FILTER()プロシージャで取り出すDDLのオブジェクトパスリストを渡している箇所がポイントです
PL/SQLパッケージおよびタイプ・リファレンス - 47 DBMS_DATAPUMP

BILL> !cat extract_ddl.sql
SET VERIFY OFF
SET SERVEROUTPUT ON
DECLARE
v4Debug VARCHAR2(50);
cDirectory CONSTANT VARCHAR2(30) := UPPER('&1');
cDumpFileName CONSTANT VARCHAR2(64) := '&2'||'.dmp';
cLogFileName CONSTANT VARCHAR2(64) := '&2'||'.log';
cDdlFileName CONSTANT VARCHAR2(64) := '&2'||'.txt';
cPathList CONSTANT VARCHAR2(4000) := 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 => 'SQL_FILE'
,job_mode => 'FULL'
,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 := 'ADD_FILE - Ddlfile';
DBMS_DATAPUMP.ADD_FILE (
handle => vDataPumpJobHandle
,filename => cDdlFileName
,directory => cDirectory
,filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_SQL_FILE
);

v4Debug := 'METADATA_FILTER - schema name';
DBMS_DATAPUMP.METADATA_FILTER (
handle => vDataPumpJobHandle
,name => 'INCLUDE_PATH_LIST'
,value => '''' || cPathList || ''''
);

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。
ということで、しばらくは機嫌悪いかも、ガルーーーーーっw



Data Pumpも癖モノだよね〜w その1 - queryパラメーターの解析タイミング
Data Pumpも癖モノだよね〜w その2 - 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 ジョブの停止

| | コメント (0)

2020年7月20日 (月)

RDS Oracle 雑多なメモ#19 FAQ / DBMS_DATAPUMPパッケージ de ジョブの停止

Previously, Mac De Oracle...

RDS Oracle 雑多なメモ#18 / DBMS_DATAPUMPパッケージ de expdp/impdp

DBMS_DATAPUMPパッケージを利用した Full Export スクリプトと、ディレクトリ名は大文字で渡すことというマニュアルに記載されていない、発生するとしばらく道に迷ってしまいそうなエラーが返るというお話でした。

今日は、その続き、Data PumpジョブはコマンドラインであってもCTRL+Cでは停止することはできないことはみなさんご存知の通りだとは思います。

正しい停止手順は以下の通り

DBA_DATAPUMP_JOBSで停止したいDATAPUMPジョブ名とジョブオーナー名を確認する

4.199 DBA_DATAPUMP_JOBS
https://docs.oracle.com/cd/F19136_01/refrn/DBA_DATAPUMP_JOBS.html#GUID-141B1FA2-9DE4-4EAF-8270-630E68431DDA


expdp/impdpのattachコマンド、または、DBMS_DATAPUMP.ATTACH()ファンクションで該当ジョブへattachする

3.4.4 ATTACH (expdp/impdpとも同じ対話コマンド)
https://docs.oracle.com/cd/F19136_01/sutil/datapump-import-utility.html#GUID-ADF15D47-FD19-4794-A5C4-685740AA04F9

47.5.2 ATTACHファンクション
https://docs.oracle.com/cd/F19136_01/arpls/DBMS_DATAPUMP.html#GUID-E073EA12-363D-4A6B-9596-1E1D40EA747C


expdp/impdpのkill_jobコマンド、または、DBMS_DATAPUMP.STOP_JOB()プロシージャで該当ジョブを停止する

2.5.7 KILL_JOB (expdp/impdpとも同じ対話コマンド)
https://docs.oracle.com/cd/F19136_01/sutil/oracle-data-pump-export-utility.html#GUID-9DA12603-B6FA-4631-8DFA-B75466CAF178

47.5.16 STOP_JOBプロシージャ
https://docs.oracle.com/cd/F19136_01/arpls/DBMS_DATAPUMP.html#GUID-9CE265FA-F9C6-4816-8AE0-AD0BEF3DC3CA


コマンドラインでもAPI経由でも手順は全く同じ。利用するツールが異なるだけでなので、まずは手順を覚えることが大切なんですよね。これ。
あとは、コマンドラインでは以下のマニュアルを参照し、具体的なコマンドオプションを知る
DBMS_DATAPUMPプロシージャを利用する場合は以下のマニュアルを参照し、どのようなスクリプトになるかを知る

あとは、実践のみですよー、みなさん! :)


では、DBMS_DATAPUMPパッケージでの例を(すでにエクスポートは実行中という状況からはじめます)

BILL> @show_datapump_jobs

OWNER_NAME JOB_NAME STATE JOB_MODE OPERATION
----------------- ------------------------- ------------- ------------- -------------
BILL SYS_EXPORT_FULL_01 EXECUTING FULL EXPORT



BILL> @cancel_datapump_job SYS_EXPORT_FULL_01 BILL

PL/SQL procedure successfully completed.


BILL> @show_datapump_jobs

no rows selected

BILL> @ls_dir data_pump_dir

FILENAME TYPE FILESIZE MTIME
--------------------------- ---------- ---------- ---------
fullexp.log file 2761 18-JUL-20
datapump/ directory 4096 18-JUL-20


BILL> @cat_file data_pump_dir fullexp.log

TEXT
----------------------------------------------------------------------------------------
FLASHBACK automatically enabled to preserve database integrity.
Starting "BILL"."SYS_EXPORT_FULL_01":
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/NORMAL_OPTIONS/TABLE
Job "BILL"."SYS_EXPORT_FULL_01" stopped due to fatal error at Sat Jul 18 10:02:49 2020 elapsed 0 00:00:50

41 rows selected.


BILL> @rm_file data_pump_dir fullexp.log
DATA_PUMP_DIR/fullexp.log removed.

PL/SQL procedure successfully completed.

BILL> @ls_dir data_pump_dir

FILENAME TYPE FILESIZE MTIME
--------------------------- ---------- ---------- ---------
datapump/ directory 4096 18-JUL-20

停止できましたー


今回利用したDATAPUMPジョブ停止スクリプトの例は以下のとおり。

BILL> !cat show_datapump_jobs.sql

col owner_name for a30
col job_name for a30
col state for a30
col job_mode for a30
col operation for a30
SELECT
owner_name
, job_name
, state
, job_mode
, operation
FROM
DBA_DATAPUMP_JOBS
ORDER BY
owner_name
, job_name
, state
;


BILL> !cat cancel_datapump_job.sql

DECLARE
hdl NUMBER;
BEGIN
hdl := DBMS_DATAPUMP.ATTACH(
job_name => UPPER('&1')
,job_owner => UPPER('&2')
);

DBMS_DATAPUMP.STOP_JOB(
handle => hdl
, immediate => 1
, keep_master => 0
);
END;
/


ls_dir.sql, rm_file.sql そして、cat_file.sqlの例は過去のエントリを参照してみてください。

ls_dir.sql
http://discus-hamburg.cocolog-nifty.com/mac_de_oracle/2018/09/rds-oracle-3-fa.html

rm_file.sql
http://discus-hamburg.cocolog-nifty.com/mac_de_oracle/2018/09/rds-oracle-7-fa.html

cat_file.sql
http://discus-hamburg.cocolog-nifty.com/mac_de_oracle/2018/09/rds-oracle-4-fa.html

では、次回へつづく。




Data Pumpも癖モノだよね〜w その1 - queryパラメーターの解析タイミング
Data Pumpも癖モノだよね〜w その2 - 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

| | コメント (0)

2020年7月19日 (日)

RDS Oracle 雑多なメモ#18 / DBMS_DATAPUMPパッケージ de expdp/impdp

さて、COVIT-19の感染者も再び増加傾向にある今日この頃ですが、みなさま、お変わりありませんか?

半年ぶり以上ぶりのエントリです。(2020年のOracle ACEのKPIカウントもスタートしたのでやっと再始動)
再始動第一段目は、癖者の極み、Data Pumpネタで。

何年か前にも癖者だというエントリは書いたことがありますが、今回はコマンドラインではなく、DBMS_DATAPUMPパッケージ。そう、APIが主人公です

Data Pumpも癖モノだよね〜w その6 - schemaモードでMviewを他のPDBへ複製(オプジェクトパスが不足すると...)
http://discus-hamburg.cocolog-nifty.com/mac_de_oracle/2017/04/data-pumpw6---s.html

そして最近では、

DS Oracle 雑多なメモ#1 / FAQ 〜
http://discus-hamburg.cocolog-nifty.com/mac_de_oracle/2018/09/rds-oracle-1-fa.html

から

RDS Oracle 雑多なメモ#17/ FAQ
http://discus-hamburg.cocolog-nifty.com/mac_de_oracle/2019/02/rds-oracle-17-f.html


何度かData Pumpネタを取り上げてきました、今回は RDS Oracleでは需要が多そうな
DBMS_DATAPUMPパッケージからAPIを利用してexpdp/impdp相当の操作に特化していくつご紹介しておきます。

普段はコマンドラインだけでした操作したことないData Pumpですが、いざDBMS_DATAPUMPパッケージしか使えないという状況になって、涙目状態は辛いですよね。
そんなことにならないように普段から慣れておくとかスクリプト集を用意しておくと良いと思います。

コマンドラインでも癖もとだよねぇ〜というネタのとおりDBMS_DATAPUMPパッケージでも一癖あるのはおなじですw
とはいえ、コマンドラインである程度ポイントを抑えていればある程度は感でいけますよ。

では、さっそく、DBMS_DATAPUMPパッケージの準備運動として、Data Pump Full exportを試してみましょう。
以前も同類のスクリプトは公開済みなので、過去のエントリを読んだことがあるなら簡単なはず:)

環境
Oracle Client 19c (SQL*Plusパッケージが含まれているパッケージ)
https://www.oracle.com/jp/database/technologies/instant-client.html

AWS RDS Oracle Database 19c
https://aws.amazon.com/jp/console/


実行例)

BILL> @expdp_full data_pump_dir fullexp
FLASHBACK automatically enabled to preserve database integrity.
Starting "BILL"."SYS_EXPORT_FULL_01":
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/VIEW/VIEW
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TRIGGER
Processing object type DATABASE_EXPORT/SCHEMA/EVENT/TRIGGER
Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/AUDIT_UNIFIED/AUDIT_POLICY_ENABLE
Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER
. . exported "SYS"."KU$_USER_MAPPING_VIEW" 5.921 KB 28 rows

...中略...

. . exported "SCOTT"."TAB31" 541.6 MB 2000000 rows
. . exported "SCOTT"."TAB311" 521.6 MB 2000000 rows
. . exported "SCOTT"."TAB3" 267.9 MB 1000000 rows

...中略...

. . exported "HOGE"."HOGE" 5.093 KB 1 rows

...中略...

Master table "BILL"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for BILL.SYS_EXPORT_FULL_01 is:
/rdsdbdata/datapump/fullexp.dmp
Job "BILL"."SYS_EXPORT_FULL_01" successfully completed at Sat Jul 18 10:25:02 2020 elapsed 0 00:04:25

PL/SQL procedure successfully completed.

Elapsed: 00:04:27.32
BILL>
BILL> @ls_dir data_pump_dir

FILENAME TYPE FILESIZE MTIME
---------------------------------------------------------------------- ---------- ---------- ---------
datapump/ directory 4096 18-JUL-20
fullexp.log file 9071 18-JUL-20
fullexp.dmp file 1461907456 18-JUL-20

Elapsed: 00:00:00.15


なお、上記で利用している ls_dir.sql は以下エントリを参照のこと。 
RDS Oracle 雑多なメモ#3 / FAQ
http://discus-hamburg.cocolog-nifty.com/mac_de_oracle/2018/09/rds-oracle-3-fa.html


RDS Oracle 雑多なメモ#9 / FAQでも紹介しているスクリプトを多少変更してあります。
Full ExportなのでDBMS_DATAPUMP.OPEN()のjob_modeパラメータが'FULL'になっている点と、DBMS_DATAPUMP.METADATA_FILTER()でEXCLUDEやINCLUDEしていない。つまり、Full Exportになっています。
http://discus-hamburg.cocolog-nifty.com/mac_de_oracle/2018/09/rds-oracle-9-fa.html

参考)PL/SQLパッケージおよびタイプ・リファレンス
https://docs.oracle.com/cd/F19136_01/arpls/DBMS_DATAPUMP.html#GUID-AEA7ED80-DB4A-4A70-B199-592287206348

なお、マニュアルには書かれてないのですが、ディレクトリ名は、"大文字" にするのがポイントです。Case Sensitiveで大文字しか認識しません。スクリプトを注意深く見ていただけると、UPPER()を使っている部分(赤字部分)があることに気づくとおもいます。もし、小文字で渡してしまうとDBMS_DATAPUMPパッケージの癖の強いエラーメッセージを受け取りことになり、意味わからなーーーーい。としばらくは迷子になってしまうことでしょう。後半で、UPPER()なしで小文字で渡してしまった場合にはどのようなエラーが返されるのかお見せしたいと思います。


BILL> !cat expdp_full.sql

SET VERIFY OFF
SET SERVEROUTPUT ON
DECLARE
v4Debug VARCHAR2(50);
cDirectory CONSTANT VARCHAR2(30) := UPPER('&1');
cDumpFileName CONSTANT VARCHAR2(128) := '&2'||'.dmp';
cLogFileName CONSTANT VARCHAR2(128) := '&2'||'.log';
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 => 'EXPORT'
,job_mode => 'FULL'
,remote_link => NULL
,job_name => NULL
,version => 'LATEST'
);

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 := '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
SET SERVEROUTPUT OFF
SET VERIFY ON

では、少しだけ前振りしていた、もしも、ディレクトリ名を小文字で渡してしまったら....以下のようなエラーが返されます。

なかなかの癖者なエラーメッセージですよね。このエラーですぐに問題点に気づけたら達人に域ですw (癖者な人よりは扱いやすいとは思いますがw ただのAPIなので)

BILL> @expdp_full data_pump_dir fullexp2
old 3: cDirectory CONSTANT VARCHAR2(30) := '&1';
new 3: cDirectory CONSTANT VARCHAR2(30) := 'data_pump_dir';
old 4: cDumpFileName CONSTANT VARCHAR2(64) := '&2'||'.dmp';
new 4: cDumpFileName CONSTANT VARCHAR2(64) := 'fullexp2'||'.dmp';
old 5: cLogFileName CONSTANT VARCHAR2(64) := '&2'||'.log';
new 5: cLogFileName CONSTANT VARCHAR2(64) := 'fullexp2'||'.log';
ORA-39001: invalid argument value
ADD_FILE - dumpfile
DECLARE
*
ERROR at line 1:
ORA-39001: invalid argument value
ORA-06512: at line 86
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4929
ORA-06512: at "SYS.DBMS_DATAPUMP", line 5180
ORA-06512: at line 26

では、次回へつづく。



Data Pumpも癖モノだよね〜w その1 - queryパラメーターの解析タイミング
Data Pumpも癖モノだよね〜w その2 - 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

| | コメント (0)