How to Gather the Header Information and the Content of an Export Dumpfile?

I’ve found a great document at My Oracle Support (Metalink): How to Gather the Header Information and the Content of an Export Dumpfile? [ID 462488.1]. The document explains how to extract DDL statements from dumpfiles and how to use GET_DUMPFILE_INFO procedure (of DBMS_DATAPUMP package) to gather header information for both original and datapump exports.

In summary,

The Data Definition Language (DDL) statements in a DataPump dumpfile can be extracted with the parameter SQLFILE:

impdp DIRECTORY=testdir DUMPFILE=test.dmp SQLFILE=ddlcommands.sql FULL=y

1	impdp DIRECTORY=testdir DUMPFILE=test.dmp SQLFILE=ddlcommands.sql FULL=y

Note: This command will not import the data, but it still needs a valid database connection.

The Data Definition Language (DDL) statements in a original export file can be extracted with the parameter SHOW:

imp FILE=test.dmp LOG=test.log FULL=y SHOW=y

1	imp FILE=test.dmp LOG=test.log FULL=y SHOW=y

Sample PL/SQL code which uses DBMS_DATAPUMP.GET_DUMPFILE_INFO can be found at MOS [ID 462488.1]. Unfortunately this procedure can only be used in an Oracle10g Release 2 or any higher release database.

After I’ve made some tests with PL/SQL code, I decided to examine Oracle export files and write my own utility to read the header information.

So I wrote a simple Perl script (dumpinfo.pl). You can get the full source code from github:

Sample outputs:

$ ./dumpinfo.pl testdir/gokhan.dmp

dumpinfo (C) 2011 Gokhan Atil http://www.gokhanatil.com

 ........Filetype = Datapump dumpfile
 ......DB Version = 10.02.00.03.0
 File Version Str = 1.1
 File Version Num = 257
 ........Job Guid = a73d207ae6b75ceee04400144fad4c76
 Master Table Pos = 21
 Master Table Len = 144144
 ......Charset ID = 39
 ...Creation date = 7-4-2011 13:50:33
 ........Job Name = "GOKHAN"."SYS_EXPORT_SCHEMA_01"
 ........Platform = SVR4-be-64bit-8.1.0
 ........Language = WE8ISO8859P9
 .......Blocksize = 4096

$ ./dumpinfo.pl testdir/test.dmp

dumpinfo (C) 2011 Gokhan Atil http://www.gokhanatil.com

 ........Filetype = Classic Export file
 ..Export Version = 10.02.01
 .....Direct Path = 1 (Direct Path)
 ...Creation date = Tue Jul 5 10:53:29 2011

$ ./dumpinfo.pl testdir/gokhan.dmp

........Filetype = Datapump dumpfile

......DB Version = 10.02.00.03.0

File Version Str = 1.1

File Version Num = 257

........Job Guid = a73d207ae6b75ceee04400144fad4c76

Master Table Pos = 21

Master Table Len = 144144

......Charset ID = 39

...Creation date = 7-4-2011 13:50:33

........Job Name = "GOKHAN"."SYS_EXPORT_SCHEMA_01"

........Platform = SVR4-be-64bit-8.1.0

........Language = WE8ISO8859P9

.......Blocksize = 4096

$ ./dumpinfo.pl testdir/test.dmp

........Filetype = Classic Export file

..Export Version = 10.02.01

.....Direct Path = 1 (Direct Path)

...Creation date = Tue Jul 5 10:53:29 2011

Please send me your feedback (and bug reports).

Please share

3 Comments

Kyle Korkhouse

December 28, 2012 Reply

Wow, this is awesome. I’ve been playing around with DBMS_DATAPUMP.GET_DUMPFILE_INFO off and on for weeks and this is a much better option for my needs. Thank you for your hard work and sharing this! – Kyle K.
Seb Morineau

December 9, 2013 Reply

Hi

Thank you for sharing this very useful script. This is the kind of tool that makes you wonder how working with dumps was before…

Saves lots of time and clears doubt, thanks again!
Ken

January 7, 2016 Reply

Hi,

This is very useful solution.

Ken

Leave Comment Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.