Oracle Exp/Imp 常见问题

一 使用 EXP/IMP 前应该考虑的因素：

EXP

1

如果通过 DBCA 界面方式创建的数据库，那么 DBCA 会自动创建执行 IMP/EXP 所需的 视图 和 角色 。

如果数据库是通过 手动创建 ( create database*** ) ，那么在执行 IMP/EXP 之前，必须首先执行 cataxp.sql 或 catalog.sql ( 包含调用 cataexp.sql 的语句 ) 。

cataxp.sql 脚本文件中主要执行以下任务：

1) 创建执行IMPORT/EXP 所需的数据字典及相关视图；

2) 创建EXP_FULL_DATABASE 角色并授予相关权限，拥有该角色的用户能够执行整库的导出；

3) 创建IMP_FULL_DATABASE 角色并授予相关权限，拥有该角色的用户能够执行整库的导入；

4) 将EXP_FULL_DATABASE/IMP_FULL_DATABASE 两个角色授予DBA ，注意这个DBA 不能指操作作者本人，而是Oracle 数据库中的角色，也就是管理员角色。

2 授予权限：

执行 IMP/EXP 的用户至少要有 CREATE SESSION 权限，即连接数据库的权限，该权限包含在 CONNECT 角色中。

默认情况下，用户只能导出自己的表，要导出其他 SCHEMA 拥有的表，执行导出的用户还必须拥有 EXP_FULL_DATABASE 角色，如果导入其他 SCHEMA ，执行导入用户必须拥有 IMP_FULL_DATABASE 角色。

grant create session to scott;

grant exp_full_database to scott;

grant imp_full_database to scott;

IMP

EXP 命令是向 Dump 文件中写数据，而 IMP 命令则是从 Dump 文件中读数据。

导入之前应该考虑的因素：

1) 生成DUMP 文件的数据库版本，如果比目标服务器数据库版本高，那么导入可能失败；

2) 生成DUMP 文件的用户及该用户拥有的角色，如果导出时是DBA 用户，或拥有EXP_FULL_DATABASE 角色的用户执行的全库导出，那么导入时的用户也必须要拥有相应的权限；

3) 导出的数据库中是否含有 LOB 类型，是否有 分区表，分区索引，外部表 等，如果有，可能在导入前也需要做一些准备工作；

4) 源数据默认表空间和目标数据默认表空间是否相同；

二 EXP/IMP 的调用方式：

EXP

1) 交互式

exp help=y

2) 参数文件方式

IMP/EXP 命令都支持 PARFILE 参数，该参数的作用是指定一个参数文件。指定的参数文件是一个文本格式的文件

例如：

vim parameter.dat

file=/home/oracle/scott.dmp

indexes=n

buffer=20480000

......

exp scott/tiger parfile=parameter.dat

当参数过多，或字符串过长等适合用交参数文件方式。

当参数文件和命令参数有冲突时，一般以最后读到的参数为准。

3) 命令行方式

IMP

1) 交互式

2) 参数文件方式

3) 命令行方式

三 EXP 处理模式

EXP

1) 表模式

exp scott/tiger file=a.dmp log=a.log tables=emp compress=n

exp scott/tiger file=a.dmp log=a.log tables=(emp,dept) compress=n

exp scott/tiger file=a.dmp log=a.log tables=emp query="'where deptno=20 and ename!="SCOTT"'" compress=n

exp scott/tiger parfile=parameter.txt compress=n

vim parameter.txt

tables=emp

query='where deptno=20 and ename!="SCOTT"'

2) 用户模式

exp scott/tiger file=a.dmp log=a.log owner=scott compress=n

exp scott/tiger file a.dmp log=a.log owner=(scott,chen) compress=n

3) 整库模式

exp scott/tiger file=full.dmp log=full.log full=y

只是逻辑上的全库，只导出了和用户数据相关的对象。

4) 表空间模式

IMP

1) 表模式

2) 用户模式

3) 整库模式

4) 表空间模式

四 EXP 常见问题

影响 EXP/IMP 导出导入的因素：

1)compress=N

在执行 exp 导出的时候，如果不指定 compress=N 会遇到的问题 ( 默认初始值是 Y) ，那么 exp 会尝试压缩表的 extent ：根据表中所有的 extent 的大小创建一个很大的初始 extent ，将表的全部 extent 压缩到一个extent 中。如果利用这样导出的 dmp 文件再导入到目标库的话，目标库创建的对应的表的初始 extent 将会非常大，这样即使 truncate 这张表后，保留的使用的空间依然会很大。

2)EXP-00091: Exporting questionable statistics.

出现场景： EXP-00091 错误常在 crontab 执行脚本时出现；

问题原因： 执行导出命令的客户端 NLS_LANG 参数未设定或设定不合理；

解决方法：

查询目标数据库的字符集

select * from v$nls_parameters where parameter='NLS_CHARACTERSET';

然后在执行 exp 命令的客户端设置 NLS_LANG 变量；

3)ORA-00904

执行 EXP 命令的客户端数据库版本不能高于服务器端数据库版本；否则含 LOB 自动的数据在导出时可能会失败；

4) 执行导出莫名停顿，但 dump 文件大小疯涨

原因： 导出外部表时，如果外部表是通过 ORACLE_LOADER 方式创建的，那么在导出时只导定义，不导数据；

如果外部表时通过 ORACLE_DATAPUMP(10g) 导出，不仅不导出数据，导出表定义会触发 BUG ，使得 DUMP 疯涨， 10.2.0.3 以后已经修改了 BUG 。

5) 导出文件过大，超出文件系统限制怎么办？

不同的文件系统，支持的单个文件最大容量不同。

例如：

FAT16 ：单个文件不超过 2GB

FAT32 ：单个文件不超过 4GB

NTFS ：单个文件不超过 2TB

Ext2 、 Ext3 ：单个文件不超过 2TB

通过参数 FILESIZE 可以限制单个文件大小 , 默认单位为字节

exp scott/tiger filesize=100M file=scott.dmp log=scott.dmp owner=scott compress=n

SQL> select sum(bytes) from user_segments;

SQL> select owner,sum(bytes) from dba_segments where owner in(CHEN,SCOTT) group by owner order by 2 desc;

6) 试试能否更快的导出？

导出方式：

1) 常规路径 (Conventional Path)

2) 直接路径 (Direct Path)

常规路径 (Conventional Path) 导出是 EXP 默认的导出方式 ，在这种方式下， EXP 要处理的数据需要经过 SQL SELECT 语句的方式提取，将数据读取到缓冲池，经由 Evaluatin Buffer 处理后返回到 Export 客户端，最后被导入到 DUMP 文件。

要提供处理速度，最重要的参数是 BUFFER 。该参数用于指定执行导出时，处理数据所用的缓冲区的大小，以字节为单位。这个参数变相用来控制导出时记录数组单次最大能加载的记录数。

缓冲区大小与加载记录数之间可以通过下列公式换算：

缓冲区大小 = 记录数组大小×记录行最大值

例如：

buffer 默认是4096 字节

当导出大表时，可以加大 buffer, 提高导出速度

exp chen/chen file=a.dmp log=a.dmp owner=chen buffer=5120000 compress=n

直接路径 (Direct Path)

直接路径 (Direct Path) 也需要先把数据加载到数据库缓冲池，不过不需要再经过 Evaluatin Buffer 处理，而是直接返回到 Export 客户端并写入指定的 DUMP 文件。

需要在执行 exp 命令时指定参数 DIRECT 值为Y ( 默认值为 N) ；

影响直接路径导出效率的另一个参数是 : RECORDLENGTH ，该参数用来指定 Export 的 I/O Buffer ，以字节为单位，最大不超过 65535 。

例如：

exp test/test file=a.dmp log=a.dmp owner=test direct=Y recordlength=65535 compress=n

五 IMP 常见问题

问题一：

将 test 用户下的表，导入到 chen 用户下

其中 test 用户默认表空间 test_tab ,chen 用户默认表空间 chen_tab

imp chen/chen file=test.dmp log=test.log fromuser=test touser=chen

由于导入时，会将 scott.dmp 的存储定义 也导入到 chen 用户下，即导入后 chen 用户下的表默认表空间并不是 chen_tab ，而是 test_tab;

其中：

如果 chen 所在数据库中有 test_tab 表空间，那么可以正常导入；如果没有，导入时会报错并且终止；

问题原因： 由于 RESOURCE 角色包含 unlimited tablespace 权限，该权限的作用是允许用户拥有 无限操作表空间存储的权限 。这可能导致在 imp 导入数据库时使得数据存储在了预想以外的表空间。

解决办法：

1) 显式的授予用户指定表空间的存储权限，希望用户操作哪个表空间，就授予哪个表空间的操作权限；

SQL> alter user chen quota unlimited on chen_tab;

2) 然后回收 Unlimited Tablespace 权限

SQL> revoke unlimited tablespace from chen;

这时在将 test 用户下的表导入到 chen 用户下时，所使用的表空间就可以是 chen_tab 了，但是前提必须加参数 IGNORE( 忽略错误 ) ，否则会终止导入；

imp chen/chen file=test.dmp log=test.log fromuser=test touser=chen ignore=y

问题二：

导入含 LOB 类型的表，且表空间和当前用户默认表空间不同

如果导入的 用户默认表空间 与数据的 原存储表空间不同 ，并且表中含有 LOB 类型 或分区表、分区索引之类的对象。这种类型的导入，处理将会更复杂，甚至无法直接使用 IGNORE 参数解决问题。

可以根据错误提示 手动创建表定义 ， 设置适合的存储参数 ；

问题三：

SEQUENCE 序列未变

如果导入的 SEQUENCE 在目标端不存在，那么不会有问题；

如果在目标端已经存在同名的 SEQUENCE 对象 ，并且在 IMP 导入时指定 IGNORE=Y 参数，那么导入可以顺利完成，不过导入的结果并不是预期想要的。

IMP 导入时指定 IGNORE=Y 参数，对于已经存在的对象会忽略创建语句导致的错误，问题在于 SEQUENCE 对象的属性恰恰是 CREATE SEQUENCE 时指定的 (IMP 并不会将创建语句转换成修改属性语句 ) ，看起来 SEQUENCE 对象导入成功，但其实 IMP 什么也没做。

解决办法：导入之前，删除同名的 SEQUENCE 对象；

导入速度的提升：

BUFFER

imp chen/chen file=scott.dmp log=scott.log fromuser=scott touser=chen buffer=40960000

六 EXP 常用参数：

1 file

2 log

3 owner

4 tables

5 full

6 buffer

7 compress

8 GRANTS: 指定是否导出对象的授权信息，默认参数值为 Y ，即默认导出；

9 INDEXES: 指是否导出表的所有，默认为 Y ；

10 CONSTRAINTS: 指是否导出表的约束，默认值 Y ；

11 TRIGGERS: 指是否导出与表相关的触发器，默认值 Y ；  

12 direct

13 recordlength

七 IMP 常用参数

1 file

2 log

3 fromuser

4 touser

5 ignore

6 buffer

八 IMP 在导入表对象时的顺序与 EXP 导出表对象的顺序相同：

1) 导入表定义，即创建表对象；

2) 导入表数据；

3) 导入索引数据；

4) 创建完整性约束、视图、过程及触发器；

5) 导入 Bitmap 、 Function-Based 以及 Domain 等类型索引；

九 对象类型：

聚簇分析，统计信息，应用程序上下文，审计信息，聚簇定义，数据库链，默认角色，维度， Directory ，外部表 ( 不含数据 ) ，索引类型， Java Resources and classes ， JOB 队列，嵌套表序列，对象的授权，用于表的对象类型定义，对象类型的定义，用户定义的同义词，用户概要文件 (profiles) ，公共同义词，外键约束，角色及授权，回滚段定义，序列，物化视图、物化视图日志及刷新组，授予的系统权限，表结构、表数据、表索引、约束、触发器及表和列的注释，表空间的定义，表空间使用限额，用户定义，用户代理，用户视图，用户定义的函数、过程、包；

参考：《涂抹 Oracle 》

欢迎关注我的微信公众号"IT小Chen"，共同学习，共同成长！！！