How to Identify / Check / Find out Oracle .dmp dumpfile is Taken Using Exp / Expdp – Data Pump

advertisements

_____________________________________________________________________________________________________________________

It is a common question or doubt which arises when you get a dump file from the source database without knowing how it is exported. It might be taken using either conventional exp utility or data pump expdp. If you have the log file along with the dump file, it is very easy to identify the utility. You just need to tail the log file and you can identify as mentioned below.

For Conventional export, the logfile ends with “Export Terminated”

$ tail -1 exp_user1.log

Export terminated successfully without warnings.

For Data pump the logfile ends with “Job”

$ tail -1 expdp_user1.log

Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 08:35:50

In case you don’t have log file with you then you can use following method to identify the dump file. The strings command in Unix will help you to identify the dump

For Conventional export dumps, it will start with “.EXPORT”

$ strings exp_user1.dmp|head

.EXPORT:V11.02.00

DSYS

RUSERS

8192

For data pump export dumps, it will start with its job details. Also it will give the database SID also in the third line(E.g: mqpprd)

$ strings expdp_user1.dmp|head

"SYS"."SYS_EXPORT_SCHEMA_01"

x86_64/Linux 2.4.xx

mqpprd

WE8ISO8859P15

If you try the dumps to import in reverse you will get following errors.

Datapump dump file with exp utility

IMP-00038: Could not convert to environment character set's handle

IMP-00000: Import terminated unsuccessfully

Conventional export dump file with expdp utility

ORA-39001: invalid argument value

ORA-39000: bad dump file specification

ORA-39143: dump file "/u07/exp_dir/exp_user1.dmp" may be an original export dump file

_____________________________________________________________________________________________________________________

Website Stats