Database Reference
In-Depth Information
For some OSs, double quotation marks appearing directly on the command line must be escaped by a backslash (
\
),
because the OS treats them as special characters. For this reason, it's much more straightforward to use a parameter file.
Here are the contents of a parameter file that uses
FLASHBACK_TIME
:
directory=dp_dir
content=metadata_only
dumpfile=inv.dmp
flashback_time="to_timestamp('24-jan-2014 07:03:00','dd-mon-yyyy hh24:mi:ss')"
Depending on your OS, the command line version of the previous example must be specified as follows:
flashback_time=\"to_timestamp\(\'24-jan-2014 07:03:00\',
\'dd-mon-yyyy hh24:mi:ss\'\)\"
This line of code should be specified on one line. Here, the code has been placed on two lines in order to fit on
the page.
You can't specify both
FLASHBACK_SCN
and
FLASHBACK_TIME
when taking an export; these two parameters are
mutually exclusive. If you attempt to use both parameters at the same time, Data Pump throws the following error
message and halts the export job:
ORA-39050: parameter FLASHBACK_TIME is incompatible with parameter FLASHBACK_SCN
Importing When Objects Already Exist
When exporting and importing data, you often import into schemas in which the objects have been created (tables,
indexes, and so on). In this situation, you should import the data but instruct Data Pump to try not to create already
existing objects.
You can achieve this with the
TABLE_EXISTS_ACTION
and
CONTENT
parameters. The next example instructs Data
Pump to append data in any tables that already exist via the
TABLE_EXISTS_ACTION=APPEND
option. Also used is the
CONTENT=DATA_ONLY
option, which instructs Data Pump not to run any DDL to create objects (only to load data):
$ impdp mv_maint/foo directory=dp_dir dumpfile=inv.dmp \
table_exists_action=append content=data_only
Existing objects aren't modified in any way, and any new data that exist in the dump file are inserted into any
tables.
You may wonder what happens if you just use the
TABLE_EXISTS_ACTION
option and don't combine it with the
CONTENT
option:
$ impdp mv_maint/foo directory=dp_dir dumpfile=inv.dmp \
table_exists_action=append
The only difference is that Data Pump attempts to run DDL commands to create objects if they exist. This doesn't
stop the job from running, but you see an error message in the output, indicating that the object already exists. Here is
a snippet of the output for the previous command:
Table "MV_MAINT"."INV" exists. Data will be appended ...
The default for the
TABLE_EXISTS_ACTION
parameter is
SKIP
, unless you also specify the parameter
CONTENT=DATA_ONLY
. If you use
CONTENT=DATA_ONLY
, then the default for
TABLE_EXISTS_ACTION
is
APPEND
.