当前位置:Gxlcms > mysql > oracleimpdp的table

oracleimpdp的table

时间:2021-07-01 10:21:17 帮助过:29人阅读

1 table_exists_action参数说明 使用imp进行数据导入时,若表已经存在,要先drop掉表,再进行导入。 而使用impdp完成数据库导入时,若表已经存在,有四种的处理方式: 1) skip:默认操作 2) replace:先drop表,然后创建表,最后插入数据 3) append:在原来

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错误。看来导入导出前后的表结构不能发生变化。

人气教程排行