Pavan DBA's Blog

Lets suppose we have a dumpfile and we have no clue from which database or atleast which version of database it is exported. (assume we don’t have a log file)

We got a request to import it to other database. As per the compatibility matrix you can import only to higher versions and for this we need to know current dumpfile version.

In this situation, the following methods will help you….

Method # 1
__________

For classic export dump files on Unix systems, you can use below command

$ cat /tmp/scott_export.dmp | head | strings

Note that scott_export.dmp is the dumpfile name in my example
Method # 2
__________

You can generate a trace file which extracts so much of information

$ impdp DIRECTORY=dp_dir DUMPFILE=scott_export.dmp NOLOGFILE=y SQLFILE=impdp_s.sql TABLES=notexist TRACE=100300

A sample output is as follows

KUPF: In kupfioReadHeader…
KUPF: 16:31:56.121: newImpFile: EXAMINE_DUMP_FILE
KUPF: 16:31:56.121: ……DB Version = 10.02.00.03.00
KUPF: 16:31:56.121: File Version Str = 1.1
KUPF: 16:31:56.121: File Version Num = 257
KUPF: 16:31:56.131: Version CapBits1 = 32775
KUPF: 16:31:56.131: ……Has Master = 0
KUPF: 16:31:56.131: ……..Job Guid = B94302C5DAB344E1876105E3295269C6
KUPF: 16:31:56.131: Master Table Pos = 0
KUPF: 16:31:56.131: Master Table Len = 0
KUPF: 16:31:56.131: …..File Number = 1
KUPF: 16:31:56.131: ……Charset ID = 46
KUPF: 16:31:56.131: …Creation date = Thu Jul 13 15:25:32 2011
KUPF: 16:31:56.141: ………..Flags = 2
KUPF: 16:31:56.141: ……Media Type = 0
KUPF: 16:31:56.141: ……..Job Name = “SYSTEM”.”SYS_EXPORT_FULL_01″
KUPF: 16:31:56.141: ……..Platform = IBMPC/WIN_NT-8.1.0
KUPF: 16:31:56.141: ……..Language = WE8ISO8859P15
KUPF: 16:31:56.141: …….Blocksize = 4096
KUPF: 16:31:56.141: newImpFile: file; /tmp/scott_export.dmp, FID; 1
KUPF: 16:31:56.151: In expandwildcard. wildcard count = 1
KUPF: In kupfxExmDmpFile…
KUPF: In kupfuExmDmpFile…
KUPF: In kupfioReadHeader…
Note : The above method I tried on a 10g version database. Plz check on a test 9i database before yourun on prod
Method # 3
__________

From Oracle 10g, you have another way to do this. We can use DBMS_DATAPUMP.GET_DUMPFILE_INFO package to read the dumpfile header where this information will be stored. For this we need to use pre-defined stored procedure.

To get the procedure script and other details, refer to MOS doc 462488.1

Note : You can also use this procedure on a 9i database, but it will not give complete details (but you will get version)

Method # 4
__________

You can use a script which is mentioned in my previous post. For the script CLICK HERE