使用imp进行数据导入时,若表已经存在,要先drop掉表,再进行导入。
而使用impdp完成数据库导入时,若表已经存在,有四种的处理方式:
1) skip:默认操作
2) replace:先drop表,然后创建表,最后插入数据
4) truncate:先truncate,然后再插入数据
2 实验预备
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
7 gg
8 hh
9 ii
10 jj
10 rows selected