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)
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.
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.
Thanks, this was the only practical example of a call to get_dumpfile_info that i could find anywhere.
ReplyDelete