Database Reference
In-Depth Information
If you attempt to run the previous command again with the same directory and the same data pump name, this
error is thrown:
ORA-31641: unable to create dump file "/oradump/inv.dmp"
You can either specify a new data pump name for the export job or use the
REUSE_DUMPFILES
parameter to direct
Data Pump to overwrite an existing dump file; for example,
$ expdp mv_maint/foo directory=dp_dir dumpfile=inv.dmp reuse_dumpfiles=y
You should now be able to run the Data Pump export regardless of an existing dump file with the same name in
the output directory. When you set
REUSE_DUMPFILES
to a value of
y
, if Data Pump finds a dump file with the same
name, it overwrites the file.
■
the default value for
REUSE_DUMPFILES
is
n
. the
REUSE_DUMPFILES
parameter is available only in Oracle 11g
and higher.
Note
Creating a Daily DDL File
Sometimes, in database environments, changes occur to database objects in unexpected ways. You may have a
developer who somehow obtains the production user passwords and decides to make a change on the fly, without
telling anybody. Or a DBA may decide not to follow the standard release process and make a change to an object
while troubleshooting an issue. These scenarios can be frustrating for production-support DBAs. Whenever there is
an issue, the first question raised is, “What changed?”
When you use Data Pump, it's fairly simple to create a file that contains all the DDL to recreate every object in
your database. You can instruct Data Pump to export or import just the metadata via the
CONTENT=METADATA_ONLY
option.
For instance, in a production environment, you can set up a daily job to capture this DDL. If there is ever a
question about what changed and when, you can go back and compare the DDL in the daily dump files.
Listed next is a simple shell script that first exports the metadata content from the database and then uses Data
Pump import to create a DDL file from that export:
#!/bin/bash
export ORACLE_SID=O12C
export ORACLE_HOME=/orahome/app/oracle/product/12.1.0.1/db_1
export PATH=$PATH:$ORACLE_HOME/bin
#
DAY=$(date +%Y_%m_%d)
SID=DWREP
#---------------------------------------------------
# First create export dump file with metadata only
expdp mv_maint/foo dumpfile=${SID}.${DAY}.dmp content=metadata_only \
directory=dp_dir full=y logfile=${SID}.${DAY}.log
#---------------------------------------------------
# Now create DDL file from the export dump file.
impdp mv_maint/foo directory=dp_dir dumpfile=${SID}.${DAY}.dmp \
SQLFILE=${SID}.${DAY}.sql logfile=${SID}.${DAY}.sql.log
#
exit 0
