querying datapump file header

Useful script to pull summary details out of the datapump file header without actually having to do an import:

   declare
     dumpfile    varchar2(256) := 'common.dmp';
     dir   varchar2(30) := 'DATA_PUMP_DIR';
     info  ku$_dumpfile_info;
     ft    number;
     h   number;
   begin
     sys.dbms_datapump.get_dumpfile_info(
                                         dumpfile,
                                         dir,
                                         info,
                                         ft
      );
   for rec in (select * from table(info)order by item_code) loop
   dbms_output.put_line(rec.item_code||' '||rec.value);
   end loop;
  end;
 /

Just replace line 2 and line 3 with your dumpfile name and directory name

output is similar to the following:

1 3.1
2 1
3 C866D80E964A444FE0430200007F187B
4 1
5 178
6 Wed Aug 29 12:28:41 2012
7 2
8 "OPS$ORACLE"."SYS_EXPORT_SCHEMA_01"
9 x86_64/Linux 2.4.xx
10 EMPM
11 WE8MSWIN1252
12 4096
14 1
15 11.02.00.02.00
16 1
17 1
18 0
19 0
20 0
21 0
22 2
the values of the first column can be looked up in the oracle docs for the DBMS_DATAPUMP package

they are pasted here to make it easy (this is for 11.2)

KU$_DFHDR_FILE_VERSION         CONSTANT NUMBER := 1;
KU$_DFHDR_MASTER_PRESENT       CONSTANT NUMBER := 2;
KU$_DFHDR_GUID                 CONSTANT NUMBER := 3;
KU$_DFHDR_FILE_NUMBER          CONSTANT NUMBER := 4;
KU$_DFHDR_CHARSET_ID           CONSTANT NUMBER := 5;
KU$_DFHDR_CREATION_DATE        CONSTANT NUMBER := 6;
KU$_DFHDR_FLAGS                CONSTANT NUMBER := 7;
KU$_DFHDR_JOB_NAME             CONSTANT NUMBER := 8;
KU$_DFHDR_PLATFORM             CONSTANT NUMBER := 9;
KU$_DFHDR_INSTANCE             CONSTANT NUMBER := 10;
KU$_DFHDR_LANGUAGE             CONSTANT NUMBER := 11;
KU$_DFHDR_BLOCKSIZE            CONSTANT NUMBER := 12;
KU$_DFHDR_DIRPATH              CONSTANT NUMBER := 13;
KU$_DFHDR_METADATA_COMPRESSED  CONSTANT NUMBER := 14;
KU$_DFHDR_DB_VERSION           CONSTANT NUMBER := 15;
KU$_DFHDR_MAX_ITEM_CODE        CONSTANT NUMBER := 20;
KU$_DFHDR_MASTER_PIECE_COUNT   CONSTANT NUMBER := 16;
KU$_DFHDR_MASTER_PIECE_NUMBER  CONSTANT NUMBER := 17;
KU$_DFHDR_DATA_COMPRESSED      CONSTANT NUMBER := 18;
KU$_DFHDR_METADATA_ENCRYPTED   CONSTANT NUMBER := 19;
KU$_DFHDR_DATA_ENCRYPTED       CONSTANT NUMBER := 20;


link to docs is here: http://docs.oracle.com/cd/E18283_01/appdev.112/e16760/d_datpmp.htm#BABJBADB

So in the example above a value of 10 is
KU$_DFHDR_INSTANCE             CONSTANT NUMBER := 10;

so this is the source database name - in our case EMPM.

This could probably be jazzed up to make it easier to use but this is a good start point.




0 comments:

Post a Comment