30 March 2015

How to differentiate the dmp files, whether the file belongs to normal classic exp or the new expdp utility?

SQL> CREATE OR REPLACE DIRECTORY DATAPUMP AS '/tmp/export';

SQL> GRANT READ, WRITE ON DIRECTORY DATAPUMP TO SCOTT;

SQL> GRANT EXP_FULL_DATABASE, IMP_FULL_DATABASE TO SCOTT;

Run the command for getting a sample expdp dmp file:
expdp scott tables=scott.emp directory=datapump dumpfile=expdp.dmp logfile=expdpemp.log

[oracle@primary ~]$ expdp scott tables=scott.emp directory=datapump dumpfile=expdp.dmp logfile=expdpemp.log

Export: Release 11.2.0.1.0 - Production on Mon Mar 30 13:02:54 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Password: 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** tables=scott.emp directory=datapump dumpfile=expdp.dmp logfile=expdpemp.log 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."EMP"                               8.570 KB      14 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  /tmp/export/expdp.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 13:03:22

Now, Run the command for getting a sample exp dmp file
exp userid=scott tables=scott.emp file=/tmp/export/exp.dmp log=/tmp/export/expemp.log

[oracle@primary ~]$ exp userid=scott tables=scott.emp file=/tmp/export/exp.dmp log=/tmp/export/expemp.log

Export: Release 11.2.0.1.0 - Production on Mon Mar 30 17:00:44 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Password: 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8MSWIN1252 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                            EMP         14 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.
[oracle@primary ~]$ cd /tmp/export/

Now we have the exp dmp and the expdp dmp files as below:
[oracle@primary export]$ ls -ltr
total 144
-rw-rw-r-- 1 oracle oracle   1345 Mar 30 13:03 expdpemp.log
-rw-rw---- 1 oracle oracle 118784 Mar 30 13:03 expdp.dmp
-rw-rw-r-- 1 oracle oracle    579 Mar 30 17:00 expemp.log
-rw-rw-r-- 1 oracle oracle  16384 Mar 30 17:00 exp.dmp

Now type the below linux OS command to open up the exported files, eg. strings filename | more


[oracle@primary export]$ strings expdp.dmp | more
"SCOTT"."SYS_EXPORT_TABLE_01"
Linuxi386/Linux-2.0.34-8.1.0
ORCL
WE8MSWIN1252
11.02.00.00.00
001:001:000001:000001


[oracle@primary export]$ strings exp.dmp | more
EXPORT:V11.02.00
DSCOTT
RTABLES
8192
                                       Mon Mar 30 17:0:48 2015/tmp/export/exp.dmp
#C##
#C##
+00:00


You can clearly make out the difference from the above files now, we can then use the suitable import utility, either imp or impdp based on your above output. Hope this was useful and informative, though a very very small article.

No comments:

Post a Comment