Oracle Exp/Imp常见问题

最新推荐文章于 2023-10-30 10:32:36 发布

ckth47350

最新推荐文章于 2023-10-30 10:32:36 发布

阅读量431

点赞数

文章标签：数据库开发工具 java

Oracle Exp/Imp常见问题

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

EXP

如果通过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)，公共同义词，外键约束，角色及授权，回滚段定义，序列，物化视图、物化视图日志及刷新组，授予的系统权限，表结构、表数据、表索引、约束、触发器及表和列的注释，表空间的定义，表空间使用限额，用户定义，用户代理，用户视图，用户定义的函数、过程、包；