Table_exists_action of Oracle impdp

1 table_exists_action parameter description

When using imp for data import, if the table already exists, drop the table before importing.

When you use impdp to import a database, if the table already exists, there are four processing methods:

1) skip: Default operation

2) replace: drop the table first, create the table, and insert data.

3) append: add data based on the original data

4) truncate: First truncate and then insert data

2. Test preparation

2.1 The sys user creates a directory object and authorizes

SQL> create directory dir_dump as '/home/Oracle ';

Directory created

SQL> grant read, write on directory dir_dump to tuser;

Grant succeeded

2.2 export data

[Oracle @ cent4 ~] $ Expdp tuser/tuser directory = dir_dump dumpfile = expdp. dmp schemas = tuser;

Export: Release 10.2.0.1.0-64bit Production on Friday, January, 2012 20:44:22

Copyright (c) 2003,200 5, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-64bit Production

With the Partitioning, OLAP and Data Mining options

Starting "TUSER". "SYS_EXPORT_SCHEMA_01": tuser/******** directory = dir_dump dumpfile = expdp. dmp schemas = tuser

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 128 KB

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE

Processing object type SCHEMA_EXPORT/TABLE/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

.. Exported "TUSER". "TAB1" 5.25 KB 5 rows

.. Exported "TUSER". "TAB2" 5.296 KB 10 rows

Master table "TUSER". "SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

**************************************** **************************************

Dump file set for TUSER. SYS_EXPORT_SCHEMA_01 is:

/Home/oracle/expdp. dmp

Job "TUSER". "SYS_EXPORT_SCHEMA_01" successfully completed at 20:47:29

2.3 view the data of two existing tables

SQL> select * from tab1;

A B

-------

1 11

2 22

3 33

4 44

5 55

SQL> select * from tab2;

A B

-------

1 aa

2 bb

3 cc

4 dd

5 ee

6 ff

7 gg

8 hh

9 ii

10 jj

10 rows selected

1 2 3 4 Next Page