当前位置:Gxlcms > mysql > Oracleimpdp的table_exists_action详解

Oracleimpdp的table_exists_action详解

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

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

linux

人气教程排行