ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > 查看dmp文件的信息

查看dmp文件的信息

原创 Oracle 作者：oracle11111 时间：2024-05-28 20:50:30 0 删除 编辑

查看dmp文件的详细信息

How to Gather the Header Information and the Content of an Export Dumpfile ? (Doc ID 462488.1)

How to Find the Database and Export/Import Version (Doc ID 175627.1)

如何确定一个dmp文件是传统export(exp)导出的还是DataPump Export(expdp)导出的？ (Doc ID 2533832.1)

查看dmp文件是什么导出的：

set serveroutput on

declare

 v_filetype    NUMBER;                 -- 0=unknown 1=expdp 2=exp 3=ext

 v_info_table  sys.ku$_dumpfile_info;  -- PL/SQL table with file info

begin

 dbms_datapump.get_dumpfile_info(

  filename => 'airline.dmp',

  directory => upper('oraclehome'),

  info_table => v_info_table, filetype => v_filetype);

  dbms_output.put_line('Filetype : ' || v_filetype);

end;

/

查看类型

Filetype : 2

查看dmp文件是什么导出的：

set serveroutput on

declare

 v_filetype    NUMBER;                 -- 0=unknown 1=expdp 2=exp 3=ext

 v_info_table  sys.ku$_dumpfile_info;  -- PL/SQL table with file info

begin

 dbms_datapump.get_dumpfile_info(

  filename => '1.dmp',

  directory => upper('orahome'),

  info_table => v_info_table, filetype => v_filetype);

  dbms_output.put_line('Filetype : ' || v_filetype);

end;

/

查看类型

Filetype : 1


mos上的查看详细信息的存储过程：

CREATE OR REPLACE PROCEDURE show_dumpfile_info(

  p_dir  VARCHAR2 DEFAULT 'DATA_PUMP_DIR',

  p_file VARCHAR2 DEFAULT 'EXPDAT.DMP')

AS

-- p_dir        = directory object where dump file can be found

-- p_file       = simple filename of export dump file (case-sensitive)

  v_separator   VARCHAR2(80) := '--------------------------------------' ||

                                '--------------------------------------';

  v_path        all_directories.directory_path%type := '?';

  v_filetype    NUMBER;                 -- 0=unknown 1=expdp 2=exp 3=ext

  v_fileversion VARCHAR2(15);           -- 0.1=10gR1 1.1=10gR2 (etc.)

  v_info_table  sys.ku$_dumpfile_info;  -- PL/SQL table with file info

  type valtype  IS VARRAY(23) OF VARCHAR2(2048);

  var_values    valtype := valtype();

  no_file_found EXCEPTION;

  PRAGMA        exception_init(no_file_found, -39211);


BEGIN


-- Dump file 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_ENCRIPTION_MODE     CONSTANT NUMBER := 22;

-- For Oracle12cR1:

--    dbms_datapump.KU$_DFHDR_COMPRESSION_ALG     CONSTANT NUMBER := 23;


-- 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;

-- For Oracle12cR1: KU$_DFHDR_MAX_ITEM_CODE       CONSTANT NUMBER := 23;


-- Show header output info:

-- ========================


  dbms_output.put_line(v_separator);

  dbms_output.put_line('Purpose..: Obtain details about export ' ||

        'dumpfile.        Version: 18-DEC-2013');

  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(23);

  FOR i in 1 .. 23 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;


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


  IF v_filetype >= 1 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 of source db..: ' || var_values(5));

      dbms_output.put_line('...Direct Path Export Mode.......: ' || var_values(13));

      dbms_output.put_line('...Export Version................: ' || var_values(15));

    ELSIF v_filetype = 1 OR v_filetype = 3 THEN

      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)',

                    '4.1', '4.1 (Oracle12c Release 1: 12.1.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 (Password and Wallet)',

                    '5', '5 (Wallet)',

        var_values(22)) INTO var_values(22) FROM dual;

      SELECT DECODE(var_values(23),

                    '2', '2 (None)',

                    '3', '3 (Basic)',

                    '4', '4 (Low)',

                    '5', '5 (Medium)',

                    '6', '6 (High)',

        var_values(23)) INTO var_values(23) FROM dual;

      IF v_filetype = 1 THEN

        dbms_output.put_line(

           'Filetype.: ' || v_filetype || ' (Export Data Pump dumpfile)');

        dbms_output.put_line(v_separator);

        dbms_output.put_line('...Database Job Version..........: ' || var_values(15));

        dbms_output.put_line('...Internal Dump File Version....: ' || var_values(1));

        dbms_output.put_line('...Creation Date.................: ' || var_values(6));

        dbms_output.put_line('...File Number (in dump file set): ' || var_values(4));

        dbms_output.put_line('...Master Present in dump file...: ' || var_values(2));

        IF dbms_datapump.KU$_DFHDR_MAX_ITEM_CODE > 15 AND v_fileversion >= '2.1' THEN

          dbms_output.put_line('...Master in how many dump files.: ' || var_values(16));

          dbms_output.put_line('...Master Piece Number in file...: ' || var_values(17));

        END IF;

        dbms_output.put_line('...Operating System of source db.: ' || var_values(9));

        IF v_fileversion >= '2.1' THEN

          dbms_output.put_line('...Instance Name of source db....: ' || var_values(10));

        END IF;

        dbms_output.put_line('...Characterset ID of source db..: ' || var_values(5));

        dbms_output.put_line('...Language Name of characterset.: ' || var_values(11));

        dbms_output.put_line('...Job Name......................: ' || var_values(8));

        dbms_output.put_line('...GUID (unique job identifier)..: ' || var_values(3));

        dbms_output.put_line('...Block size dump file (bytes)..: ' || var_values(12));

        dbms_output.put_line('...Metadata Compressed...........: ' || var_values(14));

        IF dbms_datapump.KU$_DFHDR_MAX_ITEM_CODE > 15 THEN

          dbms_output.put_line('...Data Compressed...............: ' || var_values(18));

          IF dbms_datapump.KU$_DFHDR_MAX_ITEM_CODE > 22 AND v_fileversion >= '4.1' THEN

            dbms_output.put_line('...Compression Algorithm.........: ' || var_values(23));

          END IF;

          dbms_output.put_line('...Metadata Encrypted............: ' || var_values(19));

          dbms_output.put_line('...Table Data Encrypted..........: ' || var_values(20));

          dbms_output.put_line('...Column Data Encrypted.........: ' || var_values(21));

          dbms_output.put_line('...Encryption Mode...............: ' || var_values(22));

        END IF;

      ELSE

        dbms_output.put_line(

           'Filetype.: ' || v_filetype || ' (External Table dumpfile)');

        dbms_output.put_line(v_separator);

        dbms_output.put_line('...Database Job Version..........: ' || var_values(15));

        dbms_output.put_line('...Internal Dump File Version....: ' || var_values(1));

        dbms_output.put_line('...Creation Date.................: ' || var_values(6));

        dbms_output.put_line('...File Number (in dump file set): ' || var_values(4));

        dbms_output.put_line('...Operating System of source db.: ' || var_values(9));

        IF v_fileversion >= '2.1' THEN

          dbms_output.put_line('...Instance Name of source db....: ' || var_values(10));

        END IF;

        dbms_output.put_line('...Characterset ID of source db..: ' || var_values(5));

        dbms_output.put_line('...Language Name of characterset.: ' || var_values(11));

        dbms_output.put_line('...GUID (unique job identifier)..: ' || var_values(3));

        dbms_output.put_line('...Block size dump file (bytes)..: ' || var_values(12));

        IF dbms_datapump.KU$_DFHDR_MAX_ITEM_CODE > 15 THEN

          dbms_output.put_line('...Data Compressed...............: ' || var_values(18));

          IF dbms_datapump.KU$_DFHDR_MAX_ITEM_CODE > 22 AND v_fileversion >= '4.1' THEN

            dbms_output.put_line('...Compression Algorithm.........: ' || var_values(23));

          END IF;

          dbms_output.put_line('...Table Data Encrypted..........: ' || var_values(20));

          dbms_output.put_line('...Encryption Mode...............: ' || var_values(22));

        END IF;

      END IF;

      dbms_output.put_line('...Internal Flag Values..........: ' || var_values(7));

      dbms_output.put_line('...Max Items Code (Info Items)...: ' ||

                  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;

/

 

SET serveroutput on SIZE 1000000  

SQL> exec show_dumpfile_info(p_dir=> 'orahome', p_file=> '1.dmp') 

----------------------------------------------------------------------------

Purpose..: Obtain details about export dumpfile. Version: 18-DEC-2013

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.: 1.dmp

Directory: orahome

Disk Path: /home/ora112/

Filetype.: 1 (Export Data Pump dumpfile)

----------------------------------------------------------------------------

...Database Job Version..........: 11.02.00.00.00

...Internal Dump File Version....: 3.1 (Oracle11g Release 2: 11.2.0.x)

...Creation Date.................: Tue May 28 08:32:06 2024

...File Number (in dump file set): 1

...Master Present in dump file...: 1 (Yes)

...Master in how many dump files.: 1

...Master Piece Number in file...: 1

...Operating System of source db.: x86_64/Linux 2.4.xx

...Instance Name of source db....: test

...Characterset ID of source db..: 178 (WE8MSWIN1252)

...Language Name of characterset.: WE8MSWIN1252

...Job Name......................: "ZC"."SYS_EXPORT_TABLE_01"

...GUID (unique job identifier)..: 1983D233822CF4A5E060A8C086BDB7E2

...Block size dump file (bytes)..: 4096

...Metadata Compressed...........: 1 (Yes)

...Data Compressed...............: 0 (No)

...Metadata Encrypted............: 0 (No)

...Table Data Encrypted..........: 0 (No)

...Column Data Encrypted.........: 0 (No)

...Encryption Mode...............: 2 (None)

...Internal Flag Values..........: 2

...Max Items Code (Info Items)...: 22

----------------------------------------------------------------------------

还可以考虑用操作系统的sed命令查看详细信息

exp的dmp文件：

[ora112@test ~]$ sed -n 1p airline.dmp

EXPORT:V19.00.00

[ora112@test ~]$ sed -n 2p airline.dmp

DSYSTEM

[ora112@test ~]$ sed -n 3p airline.dmp

RENTIRE

[ora112@test ~]$ sed -n 4p airline.dmp

8192

expdp的dmp文件：

[ora112@test ~]$ sed -n 1p 1.dmp

ZC"."SYS_EXPORT_TABLE_01"x86_64/Linux 2.4. test WE8MSWIN1252 11.02.00.00.00001:001:000001:000001









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

0
0
上一篇： linux网络限速（可以限制使用nfs的oracle）
下一篇： expdp默认目录
请登录后发表评论 登录
全部评论
oracle11111

注册时间：2019-05-19

  • 博文量
    81
  • 访问量
    90376

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593