Oracle impdp的table_exists_action詳解

1 table_exists_action參數說明

使用imp進行資料匯入時，若表已經存在，要先drop掉表，再進行匯入。

而使用impdp完成資料庫匯入時，若表已經存在，有四種的處理方式：

1)  skip：預設操作

2)  replace：先drop表，然後建立表，最後插入資料

3)  append：在原來資料的基礎上增加資料

4)  truncate：先truncate，然後再插入資料

2 實驗預備

2.1 sys使用者建立目錄對象，並授權

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

Directory created

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

Grant succeeded

2.2 匯出資料

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

Export: Release 10.2.0.1.0 - 64bit Production on 星期五, 06 1月, 2012 20:44:22

Copyright (c) 2003, 2005, 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/TABLE

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/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 查看已有兩張表的資料

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 下一頁