impdp的TABLE_EXISTS_ACTION參數選項

impdp的TABLE_EXISTS_ACTION參數選項

impdp有一個參數選項TABLE_EXISTS_ACTION，help=y的解釋為：

Action to take if imported object already exists.
Valid keywords are: APPEND, REPLACE, [SKIP] and TRUNCATE.
 
官方文檔有句話：
“Only objects created by the Import will be remapped. In particular, the tablespaces for preexisting tables will not be remapped if TABLE_EXISTS_ACTION is set to SKIP, TRUNCATE, or APPEND.”。
僅僅是import建立的對象會被重新對應，如果使用了SKIP、TRUNCATE或APPEND，已存表對應的資料表空間不會有變化。
 
官方文檔(Oracle? Database Utilities11g Release 2 (11.2))中對這個參數的描述如下：
這個參數目的是為了告訴impdp，試圖建立的表是否在庫中已存在。
預設值是skip，但若設定了CONTENT=DATA_ONLY，則預設值是APPEND，不是SKIP。
TABLE_EXISTS_ACTION=[SKIP | APPEND | TRUNCATE | REPLACE]
SKIP：跳過這張表，繼續下一個對象。如果CONTENT設定了DATA_ONLY參數，則不能使用SKIP。
APPEND：會載入資料至對象，但不會影響已存在的行。
TRUNCATE：刪除已存在的行，然後載入所有的資料。
REPLACE：drop已存在的表，然後create並載入資料。如果CONTENT設定了DATA_ONLY，則不能使用REPLACE。
 
還有一些其他的考慮：
1. 使用TRUNCATE或REPLACE，確保所有涉及的表行不會存在其他的參照約束關係。容易理解，例如目標表是和其他表存在外部索引鍵關聯，但只匯入這張表，就可能會破壞這種關係。
2. 使用SKIP，APPEND，TRUNCATE，已存在表的依賴對象，例如索引、grants授權、觸發器和約束，不會被修改。對於REPLACE，如果依賴對象未被顯式或隱式使用EXCLUDE排除，並且存在於dump檔案中，則會drop然後create重建。
3. 使用APPEND或TRUNCATE，會在執行操作前進行一些檢查，以確保源dump和已存表相容，包括：
(1) 若已存在的表有active的約束和觸發器，就會使用外部表格訪問的方法載入資料。如果任何行違反了約束，則載入失敗，不會有任何資料載入進來。當然，可以使用DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS來跳過這環節。
(2) 如果需要載入一些可能會違反約束的資料，可以考慮先disable這些約束，載入資料後，刪除這些有問題的記錄，然後再enable約束。
(3) 使用APPEND，資料會載入至新的空間，即使現有空間仍能重用。可以載入完成後，壓縮資料。
另外，如果資料泵發現原表和目標表不匹配(例如兩張表的列數量不同，或者目標表中有一列不在原表中)，他會比較兩張表的列名。如果兩張表至少有一個列相同，則會匯入這個列的資料(前提是資料類型相容)。這種做法也有一些限制，
(a) 如果使用network參數匯入則不能使用。
(b) 以下列的類型不能刪除：列對象、列屬性、巢狀表格列、基於主鍵的引用列。
還有就是TRUNCATE不能用於聚簇表。
 
通過對一張表使用以上四種選項的實驗，來看看區別。
 
測試表：
create table test(id number);
insert into test values(1);
commit;
expdp user_exp/user_exp directory=EXPDP_DIR dumpfile=user_exp.dmp
insert into test values(2);
commit;
此時user_exp.dmp包含test表，且有一條id=1的記錄。表中有id=1和id=2兩條記錄。
 
REPLACE選項：
impdp user_exp/user_exp TABLE_EXISTS_ACTION=replace dumpfile=user_exp.dmp directory=expdp_dir
SQL> select * from test;
        ID
----------
        1
此時表中只有id=1的記錄，說明使用dmp覆蓋了test表。
SKIP選項：
impdp user_exp/user_exp TABLE_EXISTS_ACTION=skip dumpfile=user_exp.dmp directory=expdp_dir
ORA-39151: Table "USER_EXP"."TEST" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
SQL> select * from test;
        ID
----------
        1
        2
此時匯入報錯，說明是skip了已存在的對象，test表仍保持原狀。

APPEND選項：
impdp user_exp/user_exp TABLE_EXISTS_ACTION=append dumpfile=user_exp.dmp directory=expdp_dir
ORA-39152: Table "USER_EXP"."TEST" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
SQL> select * from test;
        ID
----------
        1
        2
        1
雖然報錯，但仍插入了test記錄，報錯提示了資料會append附加至已存在表中，但若有依賴關係的中繼資料，則會忽略。
 
TRUNCATE選項：
impdp user_exp/user_exp TABLE_EXISTS_ACTION=truncate dumpfile=user_exp.dmp directory=expdp_dir
ORA-39153: Table "USER_EXP"."TEST" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
SQL> select * from test;
        ID
----------
        1
報錯提示對象已被truncate，但若有依賴關係的中繼資料，會被忽略。

利用Oracle內建的impdp和expdp進行簡單備份

Oracle impdp的skip_constraint_errors選項跳過唯一約束錯誤

expdp/impdp 使用version參數跨版本資料移轉