ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 获取exp/expdp dumpfile的metadata信息

获取exp/expdp dumpfile的metadata信息

原创 Linux操作系统 作者:myownstars 时间:2012-06-15 15:16:15 0 删除 编辑

 对于使用exp/expdp导出的dmp文件,可以使用以下几种方式取得其metadataDDL

1 获取Expdp dumpfile中的DDL

可以使用impdpsqlfile选项,将其中的DDL尽数倒入该选项指定的文件中,此过程并不会真正执行impdp操作。

-- create an export Data Pump dumpfile: 
% expdp system/manager DIRECTORY=my_dir DUMPFILE=expdp_s.dmp \
LOGFILE=expdp_s.log SCHEMAS=scott 
-- create a script file of this Data Pump dumpfile with all DDL statements:
% impdp system/manager DIRECTORY=my_dir DUMPFILE=expdp_s.dmp \ 
NOLOGFILE=y SQLFILE=impdp_s.sql FULL=y

 

导出的sql格式如下,须做部分调整方可使用

-- CONNECT SYSTEM
-- new object type path is: SCHEMA_EXPORT/USER
CREATE USER "SCOTT" IDENTIFIED BY VALUES 'F894844C34402B67'
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP"
PASSWORD EXPIRE;

-- new object type path is: SCHEMA_EXPORT/SYSTEM_GRANT
GRANT UNLIMITED TABLESPACE TO "SCOTT";

-- new object type path is: SCHEMA_EXPORT/ROLE_GRANT
GRANT "CONNECT" TO "SCOTT";
GRANT "RESOURCE" TO "SCOTT";

-- new object type path is: SCHEMA_EXPORT/DEFAULT_ROLE
ALTER USER "SCOTT" DEFAULT ROLE ALL;

-- new object type path is: SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
-- CONNECT SCOTT

BEGIN
sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'),
export_db_name=>'M10203WA.CH.ORACLE.COM', inst_scn=>'2303625');
COMMIT;
END;
/

-- new object type path is: SCHEMA_EXPORT/TABLE/TABLE
-- CONNECT SYSTEM

 

 

2 获取exp dumpfile中的DDL

可使用impshow选项,与impdpsqlfile类似

-- create an export dumpfile with the classic export client: 

% exp system/manager FILE=exp_s.dmp LOG=exp_s.log OWNER=scott 

-- create a logfile of this dumpfile with all DDL statements: 

% imp system/manager FILE=exp_s.dmp LOG=imp_show.log FULL=y SHOW=y 

导出的DDL log格式如下

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

Export file created by EXPORT:V10.02.01 via conventional path
import done in WE8ISO8859P15 character set and AL16UTF16 NCHAR character set
. importing SYSTEM's objects into SYSTEM
. importing SCOTT's objects into SCOTT
"BEGIN "
"sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','"
"CURRENT_SCHEMA'), export_db_name=>'M10203WA.CH.ORACLE.COM', inst_scn=>'2301"
"131');"
"COMMIT; END;"
"ALTER SESSION SET CURRENT_SCHEMA= "SCOTT""
"CREATE TABLE "BONUS" ("ENAME" VARCHAR2(10), "JOB" VARCHAR2(9), "SAL" NUMBER"
", "COMM" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INI"
"TIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "U"
"SERS" LOGGING NOCOMPRESS"
. . skipping table "BONUS"
...
Import terminated successfully without warnings.

 

 

3 查看dumpfileinternal  header

每个 dumpfile都有一个header block存储metadata,从10.2.0.1开始可以使用dbms_datapump.get_dumpfile_info读取访问,

输出结果依oracle版本而异,procedure代码如下:

CREATE PROCEDURE show_dumpfile_info(
  p_dir  VARCHAR2 DEFAULT 'DATA_PUMP_DIR',
  p_file VARCHAR2 DEFAULT 'EXPDAT.DMP')
AS
-- p_dir         = directory object where dumpfile can be found
-- p_file        = simple filename of export dumpfile (case-sensitive)
  v_separator   VARCHAR2(80) := '--------------------------------------' ||
                                '--------------------------------------';
  v_path        all_directories.directory_path%type := '?';
  v_filetype    NUMBER;                 -- 0=unknown 1=expdp 2=exp
  v_fileversion VARCHAR2(15);           -- 0.1=10gR1 1.1=10gR2 2.1=11g
  v_info_table  sys.ku$_dumpfile_info;  -- PL/SQL table with file info
  type valtype  IS VARRAY(22) OF VARCHAR2(2048);
  var_values    valtype := valtype();
  no_file_found EXCEPTION;
  PRAGMA        exception_init(no_file_found, -39211);

BEGIN

-- Show generic info:
-- ==================

  dbms_output.put_line(v_separator);
  dbms_output.put_line('Purpose..: Obtain details about export ' ||
        'dumpfile.        Version: 19-MAR-2008');
  dbms_output.put_line('Required.: RDBMS version: 10.2.0.1.0 or higher');
  dbms_output.put_line('.          ' ||
        'Export dumpfile version: 7.3.4.0.0 or higher');
  dbms_output.put_line('.          ' ||
        'Export Data Pump dumpfile version: 10.1.0.1.0 or higher');
  dbms_output.put_line('Usage....: ' ||
        'execute show_dumfile_info(''DIRECTORY'', ''DUMPFILE'');');
  dbms_output.put_line('Example..: ' ||
        'exec show_dumfile_info(''MY_DIR'', ''expdp_s.dmp'')');
  dbms_output.put_line(v_separator);
  dbms_output.put_line('Filename.: ' || p_file);
  dbms_output.put_line('Directory: ' || p_dir);

-- Retrieve Export dumpfile details:
-- =================================

  SELECT directory_path INTO v_path FROM all_directories
   WHERE directory_name = p_dir
      OR directory_name = UPPER(p_dir);

  dbms_datapump.get_dumpfile_info(
           filename   => p_file,       directory => UPPER(p_dir),
           info_table => v_info_table, filetype  => v_filetype);

  var_values.EXTEND(22);
  FOR i in 1 .. 22 LOOP
    BEGIN
      SELECT value INTO var_values(i) FROM TABLE(v_info_table)
       WHERE item_code = i;
    EXCEPTION WHEN OTHERS THEN var_values(i) := '';
    END;
  END LOOP;

-- Show dumpfile details:
-- ======================
-- For Oracle10g Release 2 and higher:
--    dbms_datapump.KU$_DFHDR_FILE_VERSION        CONSTANT NUMBER := 1;
--    dbms_datapump.KU$_DFHDR_MASTER_PRESENT      CONSTANT NUMBER := 2;
--    dbms_datapump.KU$_DFHDR_GUID                CONSTANT NUMBER := 3;
--    dbms_datapump.KU$_DFHDR_FILE_NUMBER         CONSTANT NUMBER := 4;
--    dbms_datapump.KU$_DFHDR_CHARSET_ID          CONSTANT NUMBER := 5;
--    dbms_datapump.KU$_DFHDR_CREATION_DATE       CONSTANT NUMBER := 6;
--    dbms_datapump.KU$_DFHDR_FLAGS               CONSTANT NUMBER := 7;
--    dbms_datapump.KU$_DFHDR_JOB_NAME            CONSTANT NUMBER := 8;
--    dbms_datapump.KU$_DFHDR_PLATFORM            CONSTANT NUMBER := 9;
--    dbms_datapump.KU$_DFHDR_INSTANCE            CONSTANT NUMBER := 10;
--    dbms_datapump.KU$_DFHDR_LANGUAGE            CONSTANT NUMBER := 11;
--    dbms_datapump.KU$_DFHDR_BLOCKSIZE           CONSTANT NUMBER := 12;
--    dbms_datapump.KU$_DFHDR_DIRPATH             CONSTANT NUMBER := 13;
--    dbms_datapump.KU$_DFHDR_METADATA_COMPRESSED CONSTANT NUMBER := 14;
--    dbms_datapump.KU$_DFHDR_DB_VERSION          CONSTANT NUMBER := 15;
-- For Oracle11gR1:
--    dbms_datapump.KU$_DFHDR_MASTER_PIECE_COUNT  CONSTANT NUMBER := 16;
--    dbms_datapump.KU$_DFHDR_MASTER_PIECE_NUMBER CONSTANT NUMBER := 17;
--    dbms_datapump.KU$_DFHDR_DATA_COMPRESSED     CONSTANT NUMBER := 18;
--    dbms_datapump.KU$_DFHDR_METADATA_ENCRYPTED  CONSTANT NUMBER := 19;
--    dbms_datapump.KU$_DFHDR_DATA_ENCRYPTED      CONSTANT NUMBER := 20;
-- For Oracle11gR2:
--    dbms_datapump.KU$_DFHDR_COLUMNS_ENCRYPTED   CONSTANT NUMBER := 21;
--    dbms_datapump.KU$_DFHDR_ENCPWD_MODE         CONSTANT NUMBER := 22;

-- For Oracle10gR2: KU$_DFHDR_MAX_ITEM_CODE       CONSTANT NUMBER := 15;
-- For Oracle11gR1: KU$_DFHDR_MAX_ITEM_CODE       CONSTANT NUMBER := 20;
-- For Oracle11gR2: KU$_DFHDR_MAX_ITEM_CODE       CONSTANT NUMBER := 22;

  dbms_output.put_line('Disk Path: ' || v_path);

  IF v_filetype = 1 OR v_filetype = 2 THEN
    -- Get characterset name:
    BEGIN
      SELECT var_values(5) || ' (' || nls_charset_name(var_values(5)) ||
        ')' INTO var_values(5) FROM dual;
    EXCEPTION WHEN OTHERS THEN null;
    END;
    IF v_filetype = 2 THEN
      dbms_output.put_line(
         'Filetype.: ' || v_filetype || ' (Original Export dumpfile)');
      dbms_output.put_line(v_separator);
      SELECT DECODE(var_values(13), '0', '0 (Conventional Path)',
        '1', '1 (Direct Path)', var_values(13))
        INTO var_values(13) FROM dual;
      dbms_output.put_line('...Characterset ID.: ' || var_values(5));
      dbms_output.put_line('...Direct Path.....: ' || var_values(13));
      dbms_output.put_line('...Export Version..: ' || var_values(15));
    ELSIF v_filetype = 1 THEN
      dbms_output.put_line(
         'Filetype.: ' || v_filetype || ' (Export Data Pump dumpfile)');
      dbms_output.put_line(v_separator);
      SELECT SUBSTR(var_values(1), 1, 15) INTO v_fileversion FROM dual;
      SELECT DECODE(var_values(1),
                    '0.1', '0.1 (Oracle10g Release 1: 10.1.0.x)',
                    '1.1', '1.1 (Oracle10g Release 2: 10.2.0.x)',
                    '2.1', '2.1 (Oracle11g Release 1: 11.1.0.x)',
                    '3.1', '3.1 (Oracle11g Release 2: 11.2.0.x)',
        var_values(1)) INTO var_values(1) FROM dual;
      SELECT DECODE(var_values(2), '0', '0 (No)', '1', '1 (Yes)', 
        var_values(2)) INTO var_values(2) FROM dual;
      SELECT DECODE(var_values(14), '0', '0 (No)', '1', '1 (Yes)', 
        var_values(14)) INTO var_values(14) FROM dual;
      SELECT DECODE(var_values(18), '0', '0 (No)', '1', '1 (Yes)', 
        var_values(18)) INTO var_values(18) FROM dual;
      SELECT DECODE(var_values(19), '0', '0 (No)', '1', '1 (Yes)', 
        var_values(19)) INTO var_values(19) FROM dual;
      SELECT DECODE(var_values(20), '0', '0 (No)', '1', '1 (Yes)', 
        var_values(20)) INTO var_values(20) FROM dual;
      SELECT DECODE(var_values(21), '0', '0 (No)', '1', '1 (Yes)',
        var_values(21)) INTO var_values(21) FROM dual;
      SELECT DECODE(var_values(22),
                    '1', '1 (Unknown)',
                    '2', '2 (None)',
                    '3', '3 (Password)',
                    '4', '4 (Dual)',
                    '5', '5 (Transparent)',
        var_values(22)) INTO var_values(22) FROM dual;

      dbms_output.put_line('...File Version....: ' || var_values(1));
      dbms_output.put_line('...Master Present..: ' || var_values(2));
      dbms_output.put_line('...GUID............: ' || var_values(3));
      dbms_output.put_line('...File Number.....: ' || var_values(4));
      dbms_output.put_line('...Characterset ID.: ' || var_values(5));
      dbms_output.put_line('...Creation Date...: ' || var_values(6));
      dbms_output.put_line('...Flags...........: ' || var_values(7));
      dbms_output.put_line('...Job Name........: ' || var_values(8));
      dbms_output.put_line('...Platform........: ' || var_values(9));
      IF v_fileversion >= '2.1' THEN
        dbms_output.put_line('...Instance........: ' || var_values(10));
      END IF;
      dbms_output.put_line('...Language........: ' || var_values(11));
      dbms_output.put_line('...Block size......: ' || var_values(12));
      dbms_output.put_line('...Metadata Compres: ' || var_values(14));
      IF dbms_datapump.KU$_DFHDR_MAX_ITEM_CODE > 15 THEN
        dbms_output.put_line('...Data Compressed.: ' || var_values(18));
        dbms_output.put_line('...Metadata Encrypt: ' || var_values(19));
        dbms_output.put_line('...Data Encrypted..: ' || var_values(20));
        dbms_output.put_line('...Column Encrypted: ' || var_values(21));
        dbms_output.put_line('...Encrypt.pwd. mod: ' || var_values(22));
        IF v_fileversion = '2.1' or v_fileversion = '3.1' THEN
          dbms_output.put_line('...Master Piece Cnt: ' || var_values(16));
          dbms_output.put_line('...Master Piece Num: ' || var_values(17));
        END IF;
      END IF;
      IF v_fileversion >= '1.1' THEN
        dbms_output.put_line('...Job Version.....: ' || var_values(15));
      END IF;
      dbms_output.put_line('...Max Items Code..: ' ||
                  dbms_datapump.KU$_DFHDR_MAX_ITEM_CODE);
    END IF;
  ELSE
    dbms_output.put_line('Filetype.: ' || v_filetype);
    dbms_output.put_line(v_separator);
    dbms_output.put_line('ERROR....: Not an export dumpfile.');
  END IF;
  dbms_output.put_line(v_separator);

EXCEPTION
  WHEN no_data_found THEN
    dbms_output.put_line('Disk Path: ?');
    dbms_output.put_line('Filetype.: ?');
    dbms_output.put_line(v_separator);
    dbms_output.put_line('ERROR....: Directory Object does not exist.');
    dbms_output.put_line(v_separator);
  WHEN no_file_found THEN
    dbms_output.put_line('Disk Path: ' || v_path);
    dbms_output.put_line('Filetype.: ?');
    dbms_output.put_line(v_separator);
    dbms_output.put_line('ERROR....: File does not exist.');
    dbms_output.put_line(v_separator);
END;

 

11g输出结果如下

-- call procedure SHOW_DUMPFILE_INFO in SQL*Plus:

SET serveroutput on SIZE 1000000  
exec show_dumpfile_info(p_dir=> 'my_dir', p_file=> 'expdp_s.dmp') 

---------------------------------------------------------------------------- 
Purpose..: Obtain details about export dumpfile.        Version: 19-MAR-2008
Required.: RDBMS version: 10.2.0.1.0 or higher 
.          Export dumpfile version: 7.3.4.0.0 or higher 
.          Export Data Pump dumpfile version: 10.1.0.1.0 or higher 
Usage....: execute show_dumfile_info('DIRECTORY', 'DUMPFILE'); 
Example..: exec show_dumfile_info('MY_DIR', 'expdp_s.dmp') 
---------------------------------------------------------------------------- 
Filename.: expdp_s.dmp 
Directory: my_dir 
Disk Path: /bugmnt7/em/celclnx7/user/expdp/work 
Filetype.: 1 (Export Data Pump dumpfile) 
---------------------------------------------------------------------------- 
...File Version....: 2.1 (Oracle11g Release 1: 11.1.0.x)
...Master Present..: 1 (Yes)
...GUID............: AE9D4A8A85C6444F813600C00199745A
...File Number.....: 1
...Characterset ID.: 46 (WE8ISO8859P15)
...Creation Date...: Wed Mar 19 16:06:45 2008
...Flags...........: 2
...Job Name........: "SYSTEM"."SYS_EXPORT_SCHEMA_01"
...Platform........: x86_64/Linux 2.4.xx  
...Instance........: ORCL
...Language........: WE8ISO8859P15
...Block size......: 4096
...Metadata Compres: 1 (Yes)
...Data Compressed.: 0 (No)
...Metadata Encrypt: 0 (No)
...Data Encrypted..: 0 (No)
...Master Piece Cnt: 1
...Master Piece Num: 1
...Job Version.....: 11.01.00.00.00
...Max Items Code..: 20
---------------------------------------------------------------------------- 

PL/SQL procedure successfully completed.

Most items are self-explanatory. Some additional details for specific items:
GUID: this value uniquely identifies an Export Data Pump dumpfile (set).
Flags: used internally.
Block Size: standard dumpfile header size is 4 kb (cannot be modified).
Direct Path: only used in a dumpfile created with the classic export client.

对于datapump dumpfile,还可使用如下方法

-- create a SQL file with TRACE parameter value 100300 (trace file layer);
-- although this impdp job will give an error (ORA-39166: Object ... was not found)
-- a trace file will be written with the file header details we are interested in:

D:\Work\Expdp> impdp system/manager DIRECTORY=my_dir DUMPFILE=expdp_s%U.dmp
NOLOGFILE=y SQLFILE=impdp_s.sql TABLES=notexist TRACE=100300

以上命令会在BACKGROUND_DUMP_DEST目录下生成[SID]dm[number]_[PID].trc

 

对于更早版本的dumpfile,可以使用如下方法替代

% cat exp_s.dmp | head | strings

.EXPORT:V10.02.01 
DSYSTEM 
RUSERS 
8192  
                                      Thu Dec 13 13:36:26 2007exp_s.dmp 
#G#G 
#G#G 
+00:00 
BYTE 

参照文档

How to Gather the Header Information and the Content of an Export Dumpfile ? [ID 462488.1]

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15480802/viewspace-732940/,如需转载,请注明出处,否则将追究法律责任。

下一篇: ora-1555小结
请登录后发表评论 登录
全部评论

注册时间:2010-03-18

  • 博文量
    375
  • 访问量
    3040714