Data Pump expdp REUSE_DUMPFILE option: Overwrite existing dumpfile

This is the option with data pump expdp utility. Normally when you perform the export using expdp utility and if the dumpfile is present in the export directory it will throw an error “ORA-27038: created file already exists”. This situation happens when you wanted to perform the repetitive exports using the same dumpfile.
Oracle provides an option reuse_dumpfile=[Y/N] to avoid this error. You should mention the parameter value as Y to overwrite the existing dump file. By default the option considered as N. See the examples below.

Normal scenario with file already present in the export directory

$  expdp scott/tiger directory=exp_dir dumpfile=tde.dmp tables=example

Export: Release 11.1.0.7.0 - 64bit Production on Tuesday, 19 July, 2011 1:36:50

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, 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 "/home/oracle/scott/tde.dmp"
ORA-27038: created file already exists
Additional information: 1

Execute the expdp using REUSE_DUMPFILES

$ expdp scott/tiger directory=exp_dir dumpfile=tde.dmp tables=example reuse_dumpfiles=y

Export: Release 11.1.0.7.0 - 64bit Production on Tuesday, 19 July, 2011 1:46:05

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, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** directory=exp_dir dumpfile=tde.dmp tables=example reuse_dumpfiles=y
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.312 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."EXAMPLE":"EXAMPLE_P1"             441.3 KB   49999 rows
. . exported "SCOTT"."EXAMPLE":"EXAMPLE_P2"             408.3 KB   45121 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  /home/oracle/scott/tde.dmp
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

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