2025年impdp导入表数据命令（impdp导入表空间）

大家好，我是讯享网，很高兴认识大家。

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

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

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

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

2025年impdp导入表数据命令（impdp导入表空间）

相关推荐