Using datapump to migrate data between databases

Objective:

Refresh data from VMXDB01 to VMXDB02

From Source VMXDB01

Create directory object

SQL> create directory logical_export as '/u01/logical_export';

Export data:

[oracle@VMXDB01 ~]$ expdp \'/ as sysdba\' directory=logical_export dumpfile=code.dmp logfile=code.log tables=donghua.tbl_code

Export: Release 10.2.0.4.0 - 64bit Production on Sunday, 27 September, 2009 21:12:29

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01":  '/******** AS SYSDBA' directory=logical_export dumpfile=code.dmp logfile=code.log tables=donghua.tbl_code                                                         
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 5 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "DONGHUA"."TBL_CODE"                     3.553 MB   43960 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /u01/logical_export/code.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 21:12:40

To Destination VMXDB02

Create directory object

SQL> create directory logical_export as '/u01/logical_export';

Import data:

[oracle@VMXDB02 ~]$ impdp \'/ as sysdba\' directory=logical_export dumpfile=code.dmp logfile=code.log full=y content=data_only table_exists_action=truncate

Import: Release 10.2.0.4.0 - 64bit Production on Sunday, 27 September, 2009 21:16:57

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  '/******** AS SYSDBA' directory=logical_export dumpfile=code.dmp logfile=code.log full=y content=data_only table_exists_action=truncate
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "DONGHUA"."TBL_CODE"                     3.553 MB   43960 rows
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at 21:17:04

References:

TABLE_EXISTS_ACTION
Default: SKIP (Note that if CONTENT=DATA_ONLY is specified, the default is APPEND, not SKIP.)

Purpose

Tells Import what to do if the table it is trying to create already exists.

Syntax and Description

TABLE_EXISTS_ACTION={SKIP | APPEND | TRUNCATE | REPLACE}

The possible values have the following effects:

•SKIP leaves the table as is and moves on to the next object. This is not a valid option if the CONTENT parameter is set to DATA_ONLY.

•APPEND loads rows from the source and leaves existing rows unchanged.

•TRUNCATE deletes existing rows and then loads rows from the source.

•REPLACE drops the existing table and then creates and loads it from the source. This is not a valid option if the CONTENT parameter is set to DATA_ONLY.

The following considerations apply when you are using these options:

•When you use TRUNCATE or REPLACE, make sure that rows in the affected tables are not targets of any referential constraints.

•When you use SKIP, APPEND, or TRUNCATE, existing table-dependent objects in the source, such as indexes, grants, triggers, and constraints, are ignored. For REPLACE, the dependent objects are dropped and re-created from the source, if they were not explicitly or implicitly excluded (using EXCLUDE) and they exist in the source dump file or system.

•When you use APPEND or TRUNCATE, checks are made to ensure that rows from the source are compatible with the existing table prior to performing any action.

The existing table is loaded using the external tables access method if the existing tables have active constraints and triggers. However, be aware that if any row violates an active constraint, the load fails and no data is loaded.

If you have data that must be loaded, but may cause constraint violations, consider disabling the constraints, loading the data, and then deleting the problem rows before reenabling the constraints.

•When you use APPEND, the data is always loaded into new space; existing space, even if available, is not reused. For this reason, you may wish to compress your data after the load.

Restrictions

•TRUNCATE cannot be used on clustered tables or over network links.