Ever got into a scenario where you have a dump file without the logfile and not even sure what was the Oracle database version from where this was exported, the characterset, Operating system and other details of the Source Database ? Or even when you are not aware of which utility was used to export this dump, classic export or expdp ? Or does it contain Full DB dump or schema dump or table dumps ?

This post is to fetch all these details from the dumpfile.

Method:1- By directly reading the header block with read() system call:-

Just download the script attached here. Save the file and save it as dumpinfo.pl (since its a perl script) and run its as:-

perl dumpinfo.pl <DUMPFILE NAME>

If perl is not installed, a quick yum -y install perl would install everything required to run the script.

Output of this script would be something like:-

]$ perl dumpinfo.pl admin.dmp

Below is the formatted header of the dumpfile specified

 ........Filetype = Datapump dumpfile
 ......DB Version = 11.02.00.04.0
 File Version Str = 3.1
 File Version Num = 769
 ........Job Guid = 3bff694effe17518e0536838a8c0aa08
 Master Table Pos = 7
 Master Table Len = 183984
 ......Charset ID = 178
 ...Creation date = 8-9-2016 8:46:35
 ........Job Name = "ADMIN"."SYS_EXPORT_SCHEMA_01"
 ........Platform = x86_64/Linux 2.4.xx
 ........Language = WE8MSWIN1252
 .......Blocksize = 4096

So you’ll get the version, datapump or classic export, characterset, OS and other details.

Method:2- Using the DBMS_DATAPUMP.GET_DUMPFILE_INFO procedure.

The output of DBMS_DATAPUMP.GET_DUMPFILE_INFO is a bit messy so to do a little formatting we’ll create a procedure name show_dumpfile_info into SYSTEM’s schema this procedure would call DBMS_DATAPUMP.GET_DUMPFILE_INFO itself but then would add a bit of formatting to display the desired result.

Download the script to create this show_dumpfile_info procedure from here. Un-Comment the lines as per your Target DB version.

Now place your dumpfile in DATA_PUMP_DIR and ensure that system has read/write privs on this directory:-

CONNECT / as sysdba
GRANT read, write ON DIRECTORY DATA_PUMP_DIR TO system;

And then call the show_dumpfile_info procedure as:-

SET serveroutput on SIZE 1000000
exec show_dumpfile_info(p_dir=> 'DATA_PUMP_DIR', p_file=> '<dumpfile name>')

The output would be similar to:-

----------------------------------------------------------------------------
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.: <dumpfile name>
Directory: DATA_PUMP_DIR
Disk Path: /u01/app/oracle/dpump
Filetype.: 1 (Export Data Pump dumpfile)
----------------------------------------------------------------------------
...Database Job Version..........: 12.01.00.00.00
...Internal Dump File Version....: 4.1 (Oracle12c Release 1: 12.1.0.x)
...Creation Date.................: Wed Dec 18 19:13:13 2013
...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....: sPc11Sa
...Characterset ID of source db..: 178 (WE8MSWIN1252)
...Language Name of characterset.: WE8MSWIN1252
...Job Name......................: "SYSTEM"."SYS_EXPORT_TABLE_01"
...GUID (unique job identifier)..: EDD4D38724A26376E0437408DC0AAE97
...Block size dump file (bytes)..: 4096
...Metadata Compressed...........: 1 (Yes)
...Data Compressed...............: 0 (No)
...Compression Algorithm.........: 3 (Basic)
...Metadata Encrypted............: 0 (No)
...Table Data Encrypted..........: 0 (No)
...Column Data Encrypted.........: 0 (No)
...Encryption Mode...............: 2 (None)
...Internal Flag Values..........: 514
...Max Items Code (Info Items)...: 23
----------------------------------------------------------------------------

PL/SQL procedure successfully completed.

So here again you’ll get all the required details from the dumpfile.

Now the question is how to get if the dumpfile contains dump of full database or schema etc and if schema then what is the name of that schema, what’s the default tablespace etc.

For this DATAPUMP has a clause named as SQLFILE, this would write down all the DDLs/DMLs in the specified dump and thus you could fetch the details from the sql file generated.

impdp <user>/<password> DIRECTORY=DATA_PUMP_DIR DUMPFILE=<DUMPFILE NAME> SQLFILE=dump_info.sql

That’s it, run the above impdp command and it would create a file dump_info.sql, you can view that file to see what the dump has and accordingly from those DDLs can fetch all the required details.

Portion of output of dump_info.sql fetched from a schema dump:-

-- CONNECT ADMIN
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: SCHEMA_EXPORT/USER
-- CONNECT SYSTEM
 CREATE USER "ADMIN" IDENTIFIED BY VALUES 'S:093415CAFE02C9C4AD0827B16221524AA4D992913EB7FFA56EA960671E3E;95F3C64472751462'
      DEFAULT TABLESPACE "TOOLS"
      TEMPORARY TABLESPACE "TEMP";
-- new object type path: SCHEMA_EXPORT/SYSTEM_GRANT
GRANT UNLIMITED TABLESPACE TO "ADMIN";
-- new object type path: SCHEMA_EXPORT/ROLE_GRANT
 GRANT "DBA" TO "ADMIN";
-- new object type path: SCHEMA_EXPORT/DEFAULT_ROLE
 ALTER USER "ADMIN" DEFAULT ROLE ALL;
-- new object type path: SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
-- CONNECT ADMIN

BEGIN
sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), export_db_name=>'TEST11G', inst_scn=>'2777673');
COMMIT;
END;
/
-- new object type path: SCHEMA_EXPORT/TABLE/TABLE
CREATE TABLE "ADMIN"."DB_BACKUP_TIME"
   (    "TOTAL_BACKUP_TIME_SECS" NUMBER,
        "CURRENT_BACKUP" VARCHAR2(23 BYTE),
....
....

So as you could notice from the dump_info.sql, you can get the create USER statement to fetch out all the details required for loading the dump and using REMAP clauses.

That’s all folks, so next time if you get a hand on some orphan dumpfile do try these methods and you wouldn’t be needing any details from the user or the export logfile.

If you get into any issues while using the above scripts or procedures than please comment below and we would assist you accordingly.