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

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

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

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

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

そして最近では、

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

から

RDS Oracle 雑多なメモ#17/ FAQ https://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 https://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になっています。
https://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　その１ - 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