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:

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:

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:

gitHub-download-button

Sample outputs:

Please send me your feedback (and bug reports).

Please share
  •  
  •  
  •  
  •  
  •  
  •  

AWS Big Data Specialist. Oracle Certified Professional (OCP) for EBS R12, Oracle 10g and 11g. Co-author of "Expert Oracle Enterprise Manager 12c" book published by Apress. Awarded as Oracle ACE (in 2011) and Oracle ACE Director (in 2016) for the continuous contributions to the Oracle users community. Founding member, and vice president of Turkish Oracle User Group (TROUG). Presented at various international conferences including Oracle Open World.

3 Comments

  1. Kyle Korkhouse

    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.

  2. Seb Morineau

    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!

Leave a Reply to Kyle Korkhouse Cancel reply

Your email address will not be published. Required fields are marked *

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