以交互方式使用exp/imp的演示__Oracle_Export_select_character_yes_

当前位置: 数据库>oracle

以交互方式使用exp/imp的演示

来源: 互联网发布时间：2017-06-11

本文导语: Oracle 单实例从32位迁移到 64位方法在CentOS 6.4下安装Oracle 11gR2(x64) Oracle 11gR2 在VMWare虚拟机中安装步骤 Debian 下安装 Oracle 11g XE R2 Oracle导入导出expdp IMPDP详解 Oracle 10g expdp导出报错ORA-4031的解决方法 Oracle 10gr2 rac expdp 报错UDE...

总所周知，用exp/imp对数据库进行逻辑备份，包括表，用户，整个数据库，我们通常所熟悉的是使用命令行指定参数的方式来做的。下面我来演示一下不太常用的以交互方式的操作，操作很简单，就是只要输入exp/imp的命令，以交互方式提供导入导出所需的参数来完成。虽然这种方式没有实际的应用意义，但作为Oracle提供的一种方法，我们也是有必要熟悉一下的。

----------------------------华丽丽的分割线----------------------------

Oracle 单实例从32位迁移到 64位方法

在CentOS 6.4下安装Oracle 11gR2(x64)

Oracle 11gR2 在VMWare虚拟机中安装步骤

Debian 下安装 Oracle 11g XE R2

Oracle导入导出expdp IMPDP详解

Oracle 10g expdp导出报错ORA-4031的解决方法

Oracle 10gr2 rac expdp 报错UDE-00008 ORA-31626

Oracle中利用expdp/impdp备份数据库的使用说明

Oracle备份还原（expdp/impdp）

----------------------------Expdp/Impdp的相关参数----------------------------

环境：Oracle 10.2.0.1/Linux Red Hat 5.3

一、单表导出导入测试

--创建测试表

[oracle@ora10g ~]$ sqlplus zlm/zlm

SQL> select username,default_tablespace from dba_users where username='ZLM';

USERNAME DEFAULT_TABLESPACE

------------------------------ ------------------------------

ZLM

SQL> create table t1 as select * from dba_objects;

Table created.

SQL> create table t2 as select * from user_objects;

Table created.

SQL> select count(*) from t1;

COUNT(*)

----------

50355

SQL> select count(*) from t2;

COUNT(*)

----------

SQL> !

注意，由于之前指定过zlm用户的缺省表空间为ZLM，因此这2个测试表是会创建到ZLM表空间中去的

--执行导出t1表

[oracle@ora10g ~]$ exp

Export: Release 10.2.0.1.0 - Production on 11 11:04:17 2014

Username: zlm

Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

Enter array fetch buffer size: 4096 > 8192

Export file: expdat.dmp >

(1)E(ntire database), (2)U(sers), or (3)T(ables): (2)U >

Export table data (yes/no): yes >

Compress extents (yes/no): yes >

Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...

Table(T) or Partition(T:P) to be exported: (RETURN to quit) >

Export terminated successfully without warnings.

[oracle@ora10g ~]$ exp

Export: Release 10.2.0.1.0 - Production on 11 11:05:00 2014

Username: zlm

Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

Enter array fetch buffer size: 4096 > 8192 --指定buffer，默认是4k，这里指定了8k，实际使用时还可以设置更大的值，如1000000，即10M

Export file: expdat.dmp > t1.dmp

(1)E(ntire database), (2)U(sers), or (3)T(ables): (2)U >

Export table data (yes/no): yes >

Compress extents (yes/no): yes >

Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...

Table(T) or Partition(T:P) to be exported: (RETURN to quit) >

. . exporting table T1 50355 rows exported

Table(T) or Partition(T:P) to be exported: (RETURN to quit) >

Export terminated successfully without warnings.

[oracle@ora10g ~]$ exit

exit

--删除测试表t1

SQL> drop table t1 purge;

Table dropped.

SQL> select count(*) from t1;

select count(*) from t1

ERROR at line 1:

ORA-00942: table or view does not exist

--执行导入t1表

SQL> !

[oracle@ora10g ~]$ imp

Import: Release 10.2.0.1.0 - Production on 11 11:06:12 2014

Username: zlm

Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

Import file: expdat.dmp >

Enter insert buffer size (minimum is 8192) 30720>

Export file created by EXPORT:V10.02.01 via conventional path

import done in ZHS16GBK character set and AL16UTF16 NCHAR character set

List contents of import file only (yes/no): >

Import entire export file (yes/no): no >

. importing ZLM's objects into ZLM

Import terminated .

[oracle@ora10g ~]$ exit

exit

SQL> select count(*) from t1;

COUNT(*)

----------

50355

SQL> !

现在表已经成功地被重新导入到数据库中的zlm用户中，相当于利用了原来对t1表的逻辑备份，对误删除的t1表进行恢复，如果再对已经存在的t1表执行一次导入操作，会发生什么情况呢？

[oracle@ora10g ~]$ imp

Import: Release 10.2.0.1.0 - Production on 11 11:07:12 2014

Username: zlm

Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

Import file: expdat.dmp > t1.dmp

Enter insert buffer size (minimum is 8192) 30720>

Export file created by EXPORT:V10.02.01 via conventional path

import done in ZHS16GBK character set and AL16UTF16 NCHAR character set

List contents of import file only (yes/no): no >

Import entire export file (yes/no): no > yes

. importing ZLM's objects into ZLM

"CREATE TABLE "T1" ("OWNER" VARCHAR2(30), "OBJECT_NAME" VARCHAR2(128), "SUBO"

"BJECT_NAME" VARCHAR2(30), "OBJECT_ID" NUMBER, "DATA_OBJECT_ID" NUMBER, "OBJ"

"ECT_TYPE" VARCHAR2(19), "CREATED" DATE, "LAST_DDL_TIME" DATE, "TIMESTAMP" V"

"ARCHAR2(19), "STATUS" VARCHAR2(7), "TEMPORARY" VARCHAR2(1), "GENERATED" VAR"

"CHAR2(1), "SECONDARY" VARCHAR2(1)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRA"

"NS 255 STORAGE(INITIAL 6291456 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DE"

"FAULT) TABLESPACE "ZLM" LOGGING NOCOMPRESS"

这里虽然提示“无警告地成功地结束了命令”，但并没有执行真正的导入，原因是之前的t1表已经存在，默认如果发生object existance的error，那么导入就会终止，这时候我们可以通过选择“Ignore create error due to object existence (yes/no): no > ”来解决，这个交互模式中的yes就相当于我们熟悉的命令行中的参数ignore=y，表示使用追加的方式导入，并不会覆盖原来的数据，也不会因为对象已经存在而终止导入

[oracle@ora10g ~]$ imp

Import: Release 10.2.0.1.0 - Production on 11 11:07:38 2014

Username: zlm

Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

Import file: expdat.dmp > t1.dmp

Enter insert buffer size (minimum is 8192) 30720>

Export file created by EXPORT:V10.02.01 via conventional path

import done in ZHS16GBK character set and AL16UTF16 NCHAR character set

List contents of import file only (yes/no): >

Import grants (yes/no): yes >