oracleimpdp的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
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
3 replace
3.1 插⼊数据
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 导⼊数据
[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 再查看数据
SQL> select * from tab1;
A B
--- ----
1 11
2 22
3 33
4 44
查看数据，这时没有第六条数据。
另外新建的表，在备份中没有，这个表并不会被覆盖。
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
注意：即使表结构发⽣了变化，只要表名不发⽣变化。导⼊这个表时，就会被跳过。
5 append
5.1 删除部分数据
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
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 导⼊数据
[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
exitedORA-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:
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论