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


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
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google