Overwrite The Destination Dump File If It Exists During Datapump Export


How to overwrite the destination dump file if it exists during datapump export?

We can overwrite the existing destination dump file using new 11g datapump export parameter REUSE_DUMPFILES (default (N) )
Test case:

–> The dumpfile “scott.dmp” is already exists in the destination path.
$ expdp scott/tiger directory=dir dumpfile=scott.dmp logfile=scott.log tables=emp

Export: Release 11.1.0.7.0 – 64bit Production on Monday, 30 March, 2009 4:23:40

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file “/tmp/scott.dmp”
ORA-27038: created file already exists
Additional information: 1


—> To avoid an above error, we can use new 11g datapump export parameter REUSE_DUMPFILES
$ expdp scott/tiger directory=dir dumpfile=scott.dmp logfile=scott.log tables=emp REUSE_DUMPFILES=Y

Export: Release 11.1.0.7.0 – 64bit Production on Monday, 30 March, 2009 4:24:07

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
Starting “SCOTT”.”SYS_EXPORT_TABLE_01″: scott/******** directory=dir dumpfile=scott.dmp logfile=scott.log tables=emp REUSE_DUMPFILES=y
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/CONSTRAINT/REF_CONSTRAINT
. . 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/scott.dmp
Job “SCOTT”.”SYS_EXPORT_TABLE_01″ successfully completed at 04:24:20

Warning: Please do not overwrite the destination dump file if it contains a critical data
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s