oracle impdp的table_exists_action详解

简介:


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

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

5   55

查看数据,这时没有第六条数据。

另外新建的表,在备份中没有,这个表并不会被覆盖。

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

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 导入数据

[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数据出错,比如唯一键错误,这时什么数据都不能插入了。

5.3 修改表结构

SQL> alter table tab1 add (C varchar2(4));

Table altered

5.4 再导入数据

[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

这时居然出现600错误。

6 truncate

6.1 插入部分数据

SQL> insert into tab1 (a, b) values (6, 66);

1 row inserted

SQL> commit;

Commit complete

6.2 导入数据

[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

注意:新插入的数据将丢失。

6.3 改表结构

SQL> alter table tab1 add (C varchar2(4));

Table altered

6.4 再导入数据

[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

此时,一样也发生了600错误。看来导入导出前后的表结构不能发生变化。


本文转自pizibaidu 51CTO博客,原文链接:http://blog.51cto.com/pizibaidu/1691944,如需转载请自行联系原作者

相关文章
|
存储 SQL Oracle
Oracle使用expdp/impdp实现全库导入导出的整体流程
Oracle的全库导入,首先一点必须先创建数据库,创建了数据库,才能往该数据库导入所有数据。相对来说,使用Oracle进行数据导入导出还很有些“麻烦”的,大多数资料上来就是......
6775 0
Oracle使用expdp/impdp实现全库导入导出的整体流程
|
Oracle 关系型数据库 数据库
|
SQL Oracle 关系型数据库
Oracle-数据泵expdp/impdp实操
Oracle-数据泵expdp/impdp实操
288 0
|
Oracle 关系型数据库 数据库
Oracle expdp impdp导出导入命令及数据库备份
Oracle expdp impdp导出导入命令及数据库备份
254 0
|
Oracle 关系型数据库 数据库
Oracle备份与还原(Expdp/Impdp)
Oracle备份与还原(Expdp/Impdp) 运行cmd; 登陆数据库,输入命令:sqlplus 用户名/密码; 建立目录对象:  在建立目录对象前查询是否存在目录对象,若存在则不需要创建:  select * from ALL_DIRECTORIES;  若不想要自己所创建的目录对象可.
1988 0
|
Oracle 关系型数据库 数据库
Oracle 数据库impdp导入数据库版本和dmp数据库文件版本不匹配问题解决方法,ORA-39142版本号不兼容、ORA-39000转储文件说明错误解决方法
Oracle 数据库impdp导入数据库版本和dmp数据库文件版本不匹配问题解决方法,ORA-39142版本号不兼容、ORA-39000转储文件说明错误解决方法
1293 0
Oracle 数据库impdp导入数据库版本和dmp数据库文件版本不匹配问题解决方法,ORA-39142版本号不兼容、ORA-39000转储文件说明错误解决方法
|
Oracle 关系型数据库 数据库
oracle数据库impdp导入dmp文件功能演示,imp导入IMP-00038: Could not convert to environment character sets handle问题解决
oracle数据库impdp导入dmp文件功能演示,imp导入IMP-00038: Could not convert to environment character sets handle问题解决
800 0
|
运维 Oracle 关系型数据库
Oracle运维笔记之IMPDP导入数据报错ORA-00943
Oracle运维笔记之IMPDP导入数据报错ORA-00943
Oracle运维笔记之IMPDP导入数据报错ORA-00943

推荐镜像

更多