1 table_exists_action參數(shù)說(shuō)明 使用imp進(jìn)行數(shù)據(jù)導(dǎo)入時(shí)，若表已經(jīng)存在，要先drop掉表，再進(jìn)行導(dǎo)入。 而使用impdp完成數(shù)據(jù)庫(kù)導(dǎo)入時(shí)，若表已經(jīng)存在，有四種的處理方式： 1) skip：默認(rèn)操作 2) replace：先drop表，然后創(chuàng)建表，最后插入數(shù)據(jù) 3) append：在原來(lái)
1 table_exists_action參數(shù)說(shuō)明使用imp進(jìn)行數(shù)據(jù)導(dǎo)入時(shí)，若表已經(jīng)存在，要先drop掉表，再進(jìn)行導(dǎo)入。
而使用impdp完成數(shù)據(jù)庫(kù)導(dǎo)入時(shí)，若表已經(jīng)存在，有四種的處理方式：
1) skip：默認(rèn)操作
2) replace：先drop表，然后創(chuàng)建表，最后插入數(shù)據(jù)
3) append：在原來(lái)數(shù)據(jù)的基礎(chǔ)上增加數(shù)據(jù)
4) truncate：先truncate，然后再插入數(shù)據(jù)
2 實(shí)驗(yàn)預(yù)備
2.1 sys用戶創(chuàng)建目錄對(duì)象，并授權(quán)
SQL> create directory dir_dump as '/home/oracle';
Directory created
SQL> grant read, write on directory dir_dump to tuser;
Grant succeeded
2.2 導(dǎo)出數(shù)據(jù)
[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 查看已有兩張表的數(shù)據(jù)
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
3 replace
3.1 插入數(shù)據(jù)
SQL> insert into tab1 (a, b) values (6, 66);
1 row inserted
SQL> commit;
Commit complete
SQL> select * from tab1;
A B
--- ----
1 11
2 22
3 33
4 44
5 55
6 66
6 rows selected
3.2 導(dǎo)入數(shù)據(jù)
[oracle@cent4 ~]$ impdp tuser/tuser directory=dir_dump dumpfile=expdp.dmp schemas=tuser table_exists_action=replace;
Import: Release 10.2.0.1.0 - 64bit Production on 星期五, 06 1月, 2012 20:53:09
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
Master table "TUSER"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "TUSER"."SYS_IMPORT_SCHEMA_01": tuser/******** directory=dir_dump dumpfile=expdp.dmp schemas=tuser table_exists_action=replace
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TUSER"."TAB1" 5.25 KB 5 rows
. . imported "TUSER"."TAB2" 5.296 KB 10 rows
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
Job "TUSER"."SYS_IMPORT_SCHEMA_01" successfully completed at 20:53:25
3.3 再查看數(shù)據(jù)
SQL> select * from tab1;
A B
--- ----
1 11
2 22
3 33
4 44
5 55
查看數(shù)據(jù)，這時(shí)沒(méi)有第六條數(shù)據(jù)。
另外新建的表，在備份中沒(méi)有，這個(gè)表并不會(huì)被覆蓋。
4 skip
drop表tab1，tab2。
[oracle@cent4 ~]$ impdp tuser/tuser directory=dir_dump dumpfile=expdp.dmp schemas=tuser;
Import: Release 10.2.0.1.0 - 64bit Production on 星期五, 06 1月, 2012 21:34:20
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
Master table "TUSER"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "TUSER"."SYS_IMPORT_SCHEMA_01": tuser/******** directory=dir_dump dumpfile=expdp.dmp schemas=tuser
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TUSER"."TAB1" 5.25 KB 5 rows
. . imported "TUSER"."TAB2" 5.296 KB 10 rows
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
Job "TUSER"."SYS_IMPORT_SCHEMA_01" successfully completed at 21:34:25
注意：即使表結(jié)構(gòu)發(fā)生了變化，只要表名不發(fā)生變化。導(dǎo)入這個(gè)表時(shí)，就會(huì)被跳過(guò)。
5 append
5.1 刪除部分?jǐn)?shù)據(jù)
SQL> delete tab1 where a = 1;
1 row deleted
SQL> delete tab2 where a = 2;
1 row deleted
SQL> commit;
Commit complete
SQL> select * from tab1;
A B
--- ----
2 22
3 33
4 44
5 55
SQL> select * from tab2;
A B
--- ----
1 aa
3 cc
4 dd
5 ee
6 ff
7 gg
8 hh
9 ii
10 jj
9 rows selected
5.2 導(dǎo)入數(shù)據(jù)
[oracle@cent4 ~]$ impdp tuser/tuser directory=dir_dump dumpfile=expdp.dmp schemas=tuser table_exists_action=append;
Import: Release 10.2.0.1.0 - 64bit Production on 星期五, 06 1月, 2012 21:50:40
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
Master table "TUSER"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "TUSER"."SYS_IMPORT_SCHEMA_01": tuser/******** directory=dir_dump dumpfile=expdp.dmp schemas=tuser table_exists_action=append
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39152: Table "TUSER"."TAB1" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
ORA-39152: Table "TUSER"."TAB2" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
ORA-31693: Table data object "TUSER"."TAB1" failed to load/unload and is being skipped due to error:
ORA-00001: unique constraint (TUSER.PK_TAB1) violated
ORA-31693: Table data object "TUSER"."TAB2" failed to load/unload and is being skipped due to error:
ORA-00001: unique constraint (TUSER.PK_TAB2) violated
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
Job "TUSER"."SYS_IMPORT_SCHEMA_01" completed with 4 error(s) at 21:50:45
注意：只要append數(shù)據(jù)出錯(cuò)，比如唯一鍵錯(cuò)誤，這時(shí)什么數(shù)據(jù)都不能插入了。
5.3 修改表結(jié)構(gòu)
SQL> alter table tab1 add (C varchar2(4));
Table altered
5.4 再導(dǎo)入數(shù)據(jù)
[oracle@cent4 ~]$ impdp tuser/tuser directory=dir_dump dumpfile=expdp.dmp schemas=tuser table_exists_action=append;
Import: Release 10.2.0.1.0 - 64bit Production on 星期五, 06 1月, 2012 21:59:19
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
Master table "TUSER"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "TUSER"."SYS_IMPORT_SCHEMA_01": tuser/******** directory=dir_dump dumpfile=expdp.dmp schemas=tuser table_exists_action=append
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39152: Table "TUSER"."TAB1" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
ORA-39152: Table "TUSER"."TAB2" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
ORA-39014: One or more workers have prematurely exited.
ORA-39029: worker 1 with process name "DW01" prematurely terminated
ORA-31671: Worker process DW01 had an unhandled exception.
ORA-00600: internal error code, arguments: [qerxtAgentOpen_911], [3], [2], [], [], [], [], []
ORA-06512: at "SYS.KUPW$WORKER", line 1345
ORA-06512: at line 2
Job "TUSER"."SYS_IMPORT_SCHEMA_01" stopped due to fatal error at 21:59:57
這時(shí)居然出現(xiàn)600錯(cuò)誤。
6 truncate
6.1 插入部分?jǐn)?shù)據(jù)
SQL> insert into tab1 (a, b) values (6, 66);
1 row inserted
SQL> commit;
Commit complete
6.2 導(dǎo)入數(shù)據(jù)
[oracle@cent4 ~]$ impdp tuser/tuser directory=dir_dump dumpfile=expdp.dmp schemas=tuser table_exists_action=truncate;
Import: Release 10.2.0.1.0 - 64bit Production on 星期五, 06 1月, 2012 22:18:21
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
Master table "TUSER"."SYS_IMPORT_SCHEMA_03" successfully loaded/unloaded
Starting "TUSER"."SYS_IMPORT_SCHEMA_03": tuser/******** directory=dir_dump dumpfile=expdp.dmp schemas=tuser table_exists_action=truncate
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39153: Table "TUSER"."TAB1" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
ORA-39153: Table "TUSER"."TAB2" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TUSER"."TAB1" 5.25 KB 5 rows
. . imported "TUSER"."TAB2" 5.296 KB 10 rows
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
Job "TUSER"."SYS_IMPORT_SCHEMA_03" completed with 2 error(s) at 22:18:28
注意：新插入的數(shù)據(jù)將丟失。
6.3 改表結(jié)構(gòu)
SQL> alter table tab1 add (C varchar2(4));
Table altered
6.4 再導(dǎo)入數(shù)據(jù)
[oracle@cent4 ~]$ impdp tuser/tuser directory=dir_dump dumpfile=expdp.dmp schemas=tuser table_exists_action=truncate;
Import: Release 10.2.0.1.0 - 64bit Production on 星期五, 06 1月, 2012 22:22:02
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
Master table "TUSER"."SYS_IMPORT_SCHEMA_03" successfully loaded/unloaded
Starting "TUSER"."SYS_IMPORT_SCHEMA_03": tuser/******** directory=dir_dump dumpfile=expdp.dmp schemas=tuser table_exists_action=truncate
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39153: Table "TUSER"."TAB1" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
ORA-39153: Table "TUSER"."TAB2" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
ORA-39014: One or more workers have prematurely exited.
ORA-39029: worker 1 with process name "DW01" prematurely terminated
ORA-31671: Worker process DW01 had an unhandled exception.
ORA-00600: internal error code, arguments: [qerxtAgentOpen_911], [3], [2], [], [], [], [], []
ORA-06512: at "SYS.KUPW$WORKER", line 1345
ORA-06512: at line 2
Job "TUSER"."SYS_IMPORT_SCHEMA_03" stopped due to fatal error at 22:22:42
此時(shí)，一樣也發(fā)生了600錯(cuò)誤?？磥?lái)導(dǎo)入導(dǎo)出前后的表結(jié)構(gòu)不能發(fā)生變化。
聲明：本網(wǎng)頁(yè)內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問(wèn)題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com
Copyright ? 2019-2025 51dongshi.com 版權(quán)所有
違法及侵權(quán)請(qǐng)聯(lián)系：TEL:177 7030 7066 E-MAIL:11247931@qq.com 本站由北京市萬(wàn)商天勤律師事務(wù)所王興未律師提供法律服務(wù)
