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