Thursday, November 25, 2010

Oracle 11g new feature in DataPump Export: Overwrite existing dump file

As you know, until 11g, normally, Data Pump Export will return an error if you specify a dump file name that already exists. Starting with 11g, the REUSE_DUMPFILES parameter allows you to override that behavior and reuse a dump file name.

Example:
SQL> create table myt1 as select * from all_objects where rownum<11;

Table created.

SQL> create directory mydir1 as 'c:\oradump';

Directory created.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Pr
oduction
With the Partitioning option

C:\>expdp system/oracle dumpfile=myt1.dmp directory=mydir1 tables=myt1

Export: Release 11.2.0.1.0 - Production on Thu Nov 25 12:35:21 2010

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning option
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/******** dumpfile=myt1.dmp dire
ctory=mydir1 tables=myt1
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/PRE_TABLE_ACTION
. . exported "SYSTEM"."MYT1"                             11.43 KB      10 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
  C:\ORADUMP\MYT1.DMP
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 12:35:41

Now, we created dumpfile for the 1st time and lets try the same command 2nd time:

C:\>expdp system/oracle dumpfile=myt1.dmp directory=mydir1 tables=myt1

Export: Release 11.2.0.1.0 - Production on Thu Nov 25 12:35:47 2010

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Produc
tion
With the Partitioning option
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file "c:\oradump\myt1.dmp"
ORA-27038: created file already exists
OSD-04010: option specified, file already exists

Well, that (ORA-27038: created file already exist) is normal, now using new EXPDP parameter will solve the issue:REUSE_DUMPFILES

C:\>expdp system/oracle dumpfile=myt1.dmp directory=mydir1 tables=myt1 reuse_dumpfiles=y

Export: Release 11.2.0.1.0 - Production on Thu Nov 25 12:36:08 2010

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Produc
tion
With the Partitioning option
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/******** dumpfile=myt1.dmp dire
ctory=mydir1 tables=myt1 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/PRE_TABLE_ACTION
. . exported "SYSTEM"."MYT1"                             11.43 KB      10 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
  C:\ORADUMP\MYT1.DMP
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 12:36:25

C:\>

No comments: