expdpで作成したダンプファイルのスキーマ名を確認したい ( impdpのSQLFILEを使用 )

最近会社で聞かれて答えたことをまとめてみました。

客先から提供されたOracleダンプファイル(impdp/expdp)がある。エクスポート元のスキーマ名が分からない。知る方法はあるだろうか？

さて、ファイル提供元がスキーマを知らないのでスキーマを教えてもらえないという非常に特殊なシチュエーションです。

実は会社では、「公式パッケージ"DBMS_DATAPUMP"を使用し、Open時にOPERATION=>SQLFILE指定する」という方法を説明しました。そのころはimpdpだけで分かるということを知らなかったからです。ここではもっと簡単に確認できる、impdpを用いる方法を説明します。

以下、環境は11gを想定して説明しています。

概要

impdpは、公式ユーティリティexpdpによって出力(エクスポート)されたダンプファイルを、DBに投入(インポート)する公式ユーティリティです。しかし、impdpには、実際にはインポートせずに、インポート時に実行される予定のDDL群をファイルに出力するという機能を持っています。impdpのパラメータにSQLFILEを指定すると、この機能を使うことができます。ここではこの機能に特化して説明します。

この機能を含めてimpdpの詳細な説明は、公式ドキュメントに記載されています。

実験

実際に試してみることにしましょう。実験環境は、Oracle Database 11g on dockerです。

テスト用ダンプファイルの作成

まずは、テスト用のスキーマとテスト用のオブジェクトを作成します。

-- DROP USER TEST2 CASCADE;
CREATE USER TEST2 IDENTIFIED BY TEST2;
GRANT CONNECT TO TEST2;
GRANT DBA TO TEST2;
CREATE TABLE TEST2.TBL_TEST AS SELECT * FROM DUAL;
CREATE PROCEDURE TEST2.PRC_TEST IS BEGIN NULL; END;
/

これをexpdpしてみます。"docker exec -it oracle11g /bin/bash"の中で実行しています。

bash-4.2# expdp TEST2/TEST2@127.0.0.1:1521/XE DIRECTORY=DATA_PUMP_DIR DUMPFILE=sample.dat

Export: Release 11.2.0.2.0 - Production on Sat Jun 13 20:34:08 2020

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
Starting "TEST2"."SYS_EXPORT_SCHEMA_01":  TEST2/********@127.0.0.1:1521/XE DIRECTORY=DATA_PUMP_DIR DUMPFILE=sample.dat
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
. . exported "TEST2"."TBL_TEST"                          5.007 KB       1 rows
Master table "TEST2"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST2.SYS_EXPORT_SCHEMA_01 is:
  /u01/app/oracle/admin/XE/dpdump/sample.dat
Job "TEST2"."SYS_EXPORT_SCHEMA_01" successfully completed at 20:34:28

bash-4.2#

ここでは、dockerコンテナの中だけで残りの実験を進めてみます。"docker cp"でdockerコンテナの外に持ち出して、別のDB環境で試しても構いません。

SQLFILE機能の確認

以下のコマンドを使うことにします。

impdp TEST/TEST@127.0.0.1:1521/XE DUMPFILE=DATA_PUMP_DIR:sample.dat SQLFILE=DATA_PUMP_DIR:sample.sql

説明を3点しておきます。

• impdpの接続先"TEST"には、インポート対象の"TEST2"とは無関係なものを指定しています。
• パラメータ"SQLFILE"が指定されているため、DBへのデータ投入は行われません。
• パラメータ"SQLFILE"に"ディレクトリオブジェクト名:ファイル名"の表記を用いています。そのため、パラメータ"DIRECTORY"とは無関係に書き出し先を指定することができます。この機能はパラメータ"DUMPFILE"で使用できるものと同じです。

コマンド"impdp"実行前にユーザー"TEST2"を削除しておくと、「DBへのデータ投入は行われません」という部分の動きが分かりやすくなります。ここではそのあたりの説明や確認を省略しています。試す場合には、ユーザー"TEST2"を削除して、impdp実行後に作成されていないことを確認してみるとよいでしょう。

impdpを実行すると、以下のようになります。

bash-4.2# impdp TEST/TEST@127.0.0.1:1521/XE DUMPFILE=DATA_PUMP_DIR:sample.dat SQLFILE=DATA_PUMP_DIR:sample.sql

Import: Release 11.2.0.2.0 - Production on Sat Jun 13 20:51:36 2020

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
Master table "TEST"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "TEST"."SYS_SQL_FILE_FULL_01":  TEST/********@127.0.0.1:1521/XE DUMPFILE=DATA_PUMP_DIR:sample.dat SQLFILE=DATA_PUMP_DIR:sample.sql
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Job "TEST"."SYS_SQL_FILE_FULL_01" successfully completed at 20:51:37

bash-4.2#

impdp実行後、SQLFILEで指定された場所にファイルが作成されます。作成される場所はどこでしょうか。前節で実施した、コマンドexpdpの出力結果の末尾を見ると分かります。該当箇所を抜粋します。

Dump file set for TEST2.SYS_EXPORT_SCHEMA_01 is:
  /u01/app/oracle/admin/XE/dpdump/sample.dat

expdp実行時に指定したディレクトリオブジェクト"DATA_PUMP_DIR"は、ディレクトリ"/u01/app/oracle/admin/XE/dpdump/"を指しているようです。SQLFILEで指定したディレクトリオブジェクトも同じです。そのため、同じディレクトリにファイルが作成されているはずです。確認してみましょう。

bash-4.2# ls -l /u01/app/oracle/admin/XE/dpdump/
total 192
-rw-r--r-- 1 oracle dba   1572 Jun 13 20:34 export.log
-rw-r--r-- 1 oracle dba    949 Jun 13 20:51 import.log
-rw-r----- 1 oracle dba 184320 Jun 13 20:34 sample.dat
-rw-r--r-- 1 oracle dba   2020 Jun 13 20:51 sample.sql
bash-4.2#

パラメータ"SQLFILE"で指定したファイル"sample.sql"が作成されていることが分かります。作成されたファイルを見てみることにします。

bash-4.2# cat /u01/app/oracle/admin/XE/dpdump/sample.sql
-- CONNECT TEST
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: SCHEMA_EXPORT/USER
-- CONNECT SYSTEM
 CREATE USER "TEST2" IDENTIFIED BY VALUES 'S:C7580FEC4D5F508279B393FD7967A2D7C866B8FE45D15E45F74B2C3E1BB0;2E3197EF1322CB1D'
      TEMPORARY TABLESPACE "TEMP";
-- new object type path: SCHEMA_EXPORT/SYSTEM_GRANT
GRANT UNLIMITED TABLESPACE TO "TEST2";
-- new object type path: SCHEMA_EXPORT/ROLE_GRANT
 GRANT "CONNECT" TO "TEST2";
 GRANT "DBA" TO "TEST2";
-- new object type path: SCHEMA_EXPORT/DEFAULT_ROLE
 ALTER USER "TEST2" DEFAULT ROLE ALL;
-- new object type path: SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
-- CONNECT TEST2

BEGIN
sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), export_db_name=>'XE', inst_scn=>'1638367');
COMMIT;
END;
/
-- new object type path: SCHEMA_EXPORT/TABLE/TABLE
-- CONNECT TEST
CREATE TABLE "TEST2"."TBL_TEST"
   (    "DUMMY" VARCHAR2(1 BYTE)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM" ;
-- new object type path: SCHEMA_EXPORT/PROCEDURE/PROCEDURE
-- CONNECT TEST2
CREATE PROCEDURE       PRC_TEST IS BEGIN NULL; END;
/
-- new object type path: SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE

ALTER PROCEDURE "TEST2"."PRC_TEST"
  COMPILE
    PLSQL_OPTIMIZE_LEVEL=  2
    PLSQL_CODE_TYPE=  INTERPRETED
    PLSQL_DEBUG=  FALSE    PLSCOPE_SETTINGS=  'IDENTIFIERS:NONE'

 REUSE SETTINGS TIMESTAMP '2020-06-13 20:30:13'
/

DDLがファイルに書き込まれていることが分かります。

もともとの疑問に対する確認結果

もともとの疑問は何だったでしょうか。ダンプファイルに含まれているスキーマ名は何だろうか、でした。"CREATE USER"付近を抜粋してみます。

    :
-- new object type path: SCHEMA_EXPORT/USER
-- CONNECT SYSTEM
 CREATE USER "TEST2" IDENTIFIED BY VALUES 'S:C7580FEC4D5F508279B393FD7967A2D7C866B8FE45D15E45F74B2C3E1BB0;2E3197EF1322CB1D'
      TEMPORARY TABLESPACE "TEMP";
    :

パスワードは一見分からない形になっています。しかし、これを見ればユーザー"TEST2"を作成していることが分かります。

後続するオブジェクト作成時のDDLも抜粋してみます。

    :
-- CONNECT TEST
CREATE TABLE "TEST2"."TBL_TEST"
    :
-- CONNECT TEST2
CREATE PROCEDURE       PRC_TEST IS BEGIN NULL; END;
    :

スキーマ"TEST2"の下にオブジェクトを作成していることがわかります。TABLE作成時はユーザー"TEST"で接続してスキーマ"TEST2"の下に作成、PROCEDURE作成時はユーザー"TEST2"で接続して自スキーマの下に作成というように、興味深い違いが見受けられます。しかし、ここでの主題とは異なるため、気にしないでおきます。

ともあれ、これらの情報をまとめるとエクスポート元のスキーマは"TEST2"であることが分かります。

複数ユーザーがまとめてexpdpされている場合は、"CREATE USER"も複数あるはずです。同様に確認できるでしょう。

まとめ

impdpをパラメータ"SQLFILE"とともに使うことで、ダンプファイルに含まれているオブジェクトをDB投入する際のDDLがファイルに出力されます。

これによって、ダンプファイルに含まれるスキーマ名を確認することができます。また、TABLE、PROCEDURE/FUNCTION/PACKAGE、権限なども確認することができます。TABLEではSTORAGE句なども分かります。

会社ではパッケージ"DBMS_DATAPUMP"を使うという大げさなことをしてしまいました。この方法を知ったので、今後はもっと簡単に確認することができるでしょう。