How to use the REUSE_DUMPFILES Parameter using expdp in Oracle database 19C
Posted by Mir Sayeed Hassan on December 8th, 2024
How to use the REUSE_DUMPFILES Parameter using expdp in Oracle database 19C
Brief: – In case if you use existing dumpfile name, you will get an error as its already exists, To overcome this use this REUSE_DUMPFILES=YES parameter to overwrite the existing dumpfile name without error.
– By default is REUSE_DUMPFILES=NO
Check the database version.
SYS> select name, status, open_mode, version from V$database, v$instance; NAME STATUS OPEN_MODE VERSION ------------------------------------------------ ORA19CDB OPEN READ WRITE 19.0.0.0.0
Example: Consider the below files are already exist on server
[oracle@oradb19c dump_bkp1]$ ll total 1112 -rw-r-----. 1 oracle oinstall 1130496 Dec 08:14 hassan_sch.dmp -rw-r--r--. 1 oracle oinstall 2854 Dec 08 09:14 hassan_sch.log
Error
—–
[oracle@oradb19c dump_bkp1]$ expdp directory=dump_bkp1 dumpfile=hassan_sch.dmp logfile=hassan_sch.log schemas=hassan Export: Release 19.0.0.0.0 - Production on Sun Dec 08 09:36:40 2024 Version 19.18.0.0.0 Copyright (c) 1982, 2019, 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 "/u02/backup/dump_bkp1/hassan_sch.dmp" ORA-27038: created file already exists Additional information: 1
If you want to use same existing dumpfile name or you want to overwrite then you can use this parameter REUSE_DUMPFILES=YES in expdp.
[oracle@oradb19c dump_bkp1]$ expdp directory=dump_bkp1 dumpfile=hassan_sch.dmp logfile=hassan_sch.log schemas=hassan REUSE_DUMPFILES=YES Export: Release 19.0.0.0.0 - Production on Sun Dec 08 09:38:29 2024 Version 19.18.0.0.0 Copyright (c) 1982, 2019, 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=dump_bkp1 dumpfile=hassan_sch.dmp logfile=hassan_sch.log schemas=hassan REUSE_DUMPFILES=YES 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/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX . . exported "HASSAN"."BOWIE" 688.8 KB 10000 rows . . exported "HASSAN"."ADD_PICTURE" 0 KB 0 rows . . exported "HASSAN"."T1" 5.632 KB 9 rows . . exported "HASSAN"."TEST1" 5.125 KB 4 rows . . exported "HASSAN"."TEST2" 5.109 KB 2 rows Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is: /u02/backup/dump_bkp1/hassan_sch.dmp Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Sun Dec 08 09:38:55 2024 elapsed 0 00:00:23
Verify the same name as existing.
[oracle@oradb19c dump_bkp1]$ ll total 1108 -rw-r-----. 1 oracle oinstall 1130496 Dec 08 09:38 hassan_sch.dmp -rw-r--r--. 1 oracle oinstall 1893 Dec 08 09:38 hassan_sch.log