REUSE_DUMPFILES Keyword in Oracle Data Pump Export

 

REUSE_DUMPFILES parameter in expdp tells whether you want to overwrite the pre-existing dump file or not. Ideally, you will get an error message if you specify a dump file name that already exists in the respective directory. The REUSE_DUMPFILES parameter allows you to reuse a dump file name.  REUSE_DUMPFILES=[YES | NO] The default is NO. Let's try this parameter with an example. Take export backup of schema without DUMPFILE_REUSE parameter. C:\Windows\System32>expdp directory=DIR_TEST schemas=test dumpfile=SCHEMA.dmp logfile=SCHEMA.log Export: Release 19.0.0.0.0 - Production on Sat Apr 13 01:14:27 2024 Version 19.16.0.0.0 Copyright (c) 1982, 2022, Oracle and/or its affiliates.  All rights reserved. Username: / as sysdba Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Starting "SYS"."SYS_EXPORT_SCHEMA_01":  /******** AS SYSDBA directory=DIR_TEST schemas=test dumpfile=SCHEMA.dmp logfile=SCHEMA.log Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type SCHEMA_EXPORT/STATISTICS/MARKER Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/VIEW/VIEW Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT . . exported "TEST"."DEPT"                               6.531 KB       5 rows . . exported "TEST"."EMP"                                6.531 KB       5 rows . . exported "TEST"."CUSTOMERS"                              0 KB       0 rows . . exported "TEST"."PROMOTIONS"                             0 KB       0 rows Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:   C:\BACKUP\SCHEMA.DMP Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Sat Apr 13 01:15:06 2024 elapsed 0 00:00:34 Here, the dump file is created in the mentioned directory location i.e. SCHEMA.DMP  Now, try to take export of the schema again by specifying the same name and check. C:\Windows\System32>expdp directory=DIR_TEST schemas=test dumpfile=SCHEMA.dmp logfile=SCHEMA.log Export: Release 19.0.0.0.0 - Production on Sat Apr 13 01:26:42 2024 Version 19.16.0.0.0 Copyright (c) 1982, 2022, Oracle and/or its affiliates.  All rights reserved. Username: / as sysdba Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production ORA-39001: invalid argument value ORA-39000: bad dump file specification ORA-31641: unable to create dump file "C:\Backup\SCHEMA.dmp" ORA-27038: created file already exists OSD-04010: <create> option specified, file already exists Now, use REUSE_DUMPFILES keywork in your command to overwrite the dump file name. C:\Windows\System32>expdp directory=DIR_TEST schemas=test dumpfile=SCHEMA.dmp logfile=SCHEMA.log reuse_dumpfiles=y Export: Release 19.0.0.0.0 - Production on Sat Apr 13 01:28:10 2024 Version 19.16.0.0.0 Copyright (c) 1982, 2022, Oracle and/or its affiliates.  All rights reserved. Username: / as sysdba Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Starting "SYS"."SYS_EXPORT_SCHEMA_01":  /******** AS SYSDBA directory=DIR_TEST schemas=test dumpfile=SCHEMA.dmp logfile=SCHEMA.log reuse_dumpfiles=y Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type SCHEMA_EXPORT/STATISTICS/MARKER Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/VIEW/VIEW Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT . . exported "TEST"."DEPT"                               6.531 KB       5 rows . . exported "TEST"."EMP"                                6.531 KB       5 rows . . exported "TEST"."CUSTOMERS"                              0 KB       0 rows . . exported "TEST"."PROMOTIONS"                             0 KB       0 rows Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:   C:\BACKUP\SCHEMA.DMP Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Sat Apr 13 01:28:37 2024 elapsed 0 00:00:23 Here, you can see the new export dump file is created with same name by using reuse_dumpfiles=y keyword in the export command.