数据迁移中碰见的一些问题

单位有一套Oracle 9i的古老测试数据库，因为机房搬迁，所以需要迁移数据，新库是Oracle 11g了，一个比较简单的需求，但过程中碰见了一些问题，看似比较琐碎，值得总结一下。

网站建设哪家好，找成都创新互联公司！专注于网页设计、网站建设、微信开发、重庆小程序开发公司、集团企业网站建设等服务项目。为回馈新老客户创新互联还提供了西乌珠穆沁免费建站欢迎大家使用！

由于源库是9i，因此只能用imp/exp，不能用数据泵。

问题1：导入目标库用户的默认表空间

源库由于不规范的使用，对象默认存储的是数据库默认表空间USERS，既然是迁移，新库就要尽量规范一些。但问题来了，impdp/expdp可以使用remap_tablespace映射新旧表空间，exp/imp应该如何做？

网 上有一种说法是，首先收回用户user的unlimited tablespace权限，然后设置user默认表空间为bank_tbs，再将user对system和users表空间配额设置为0，意图是让imp 导入的时候，发现users表空间无权限，则自动找用户的默认表空间bank_tbs。

revoke unlimited tablespace from user;

alter user user quota unlimited on bank_tbs;

alter user user quota 0 on system;

alter user user quota 0 on users;

但从我实测看，并不是这样，可 以使用imp命令的show选项，看dmp文件内容，create table子句是会跟着tablespace users，即指定了表使用的表空间名称，由于user用户在users表空间配额为0，因此会报quota相关的错误，并不会找用户默认的 bank_tbs表空间。

我们再捋一下，

1. dump文件中有指定了tablespace users表空间。

2. 目标库存在users表空间，但用户在users表空间配额为0，其默认表空间为bank_tbs。

3. imp执行导入，报错users表空间quota错误。

用户默认表空间的作用，是若create table语句未指定tablespace子句，则会默认存储此表空间，既然如此，既然如此，又由于这是一套测试库，因此首先改一下users表空间名称，

alter tablespace users rename to users_k;

然 后执行imp导入，就可以正常存入user用户默认的bank_tbs中。顺着思路想，可以改一下数据库的默认表空间users，只要保证不存在 users表空间，dmp中create table语句就不能根据tablesapce子句，插入对应的表空间，而是找用户默认的表空间。

除此之外，可以初始化就导入users表空间，然后拼接SQL语句，将对象可以move至其他表空间，当然这就需要两倍的空间。另外还可以收工改一下dmp文件中tablespace子句对应的表空间，但只适应于小容量文件。

这里有一些知识点值得关注，

1. unlimited tablespace权限，是为用户授予resource角色是自动添加的，但从安全性的角度来考虑，在创建用户并且授予resource角色之后应该回 收unlimited tablespace这个系统权限，原因就是有了这个权限，用户可以在任意表空间中创建对象，就有可能恶意占领系统表空间，影响数据库的正常运行。

2. Oracle 9i以前，数据库默认用户的表空间是SYSTEM，这是极为不合理的，因为SYSTEM存储的是数据库重要的底层数据字典信息，如果无限制地存储用户数据，极有可能影响数据库的运行。从9i开始，默认表空间则变为了USERS，建库的时候会默认创建。

使用如下语句，可以查询当前系统默认表空间，

select property_value from database_properties where property_name = 'DEFAULT_PERMANENT_TABLESPACE';

使用如下语句，可以改下当前数据库默认的用户表空间以及临时表空间，

alter database default [temporary] tablespace tablespace_name;

问题2：数据库字符集

为了保证数据导出导入，不会出现乱码，字符集要尽量保持一致，可以使用如下语句检索当前数据库使用的字符集，

select userenv('language') from dual;

例如返回结果是AMERICAN_AMERICA.ZHS16GBK。

若要检索当前操作系统字符集，可以使用，

echo $NLS_LANG

例如返回结果是AMERICAN_AMERICA.AL32UTF8。

若要更新操作系统字符集，可以使用，

export  NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

问题3：导入过程中的一些报错

报错1：

Export file created by EXPORT:V09.02.00 via conventional path
IMP-00013: only a DBA can import a file exported by another DBA
IMP-00000: Import terminated unsuccessfully

错误信息提示，只用DBA用户可以导入另一个DBA导出的文件。意思就是这个dmp文件，导出用户是有DBA角色的，因此导入使用的用户，必须要有DBA角色。

解决方法1：使用非DBA角色的用户，重新exp导出，再用非DBA用户imp导入。

解决方法2：使用DBA用户执行imp导入操作。

相比而言，生产系统一般会选择方案1，毕竟一般业务数据的属主，不会是一个DBA角色的用户，如果用方案2，则要求目标端用户需要DBA角色，未来要是再有导出导入需求，还是需要DBA角色，无休无止了。

报错2：

Export file created by EXPORT:V09.02.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
IMP-00031: Must specify FULL=Y or provide FROMUSER/TOUSER or TABLES arguments
IMP-00000: Import terminated unsuccessfully

此时执行imp可以指定full=y，或者使用fromuser和touser参数，例如，

imp user/user file=... log=... fromuser=user touser=user

明确导出和导入的用户名称。

问题4：创建视图报错

导入日志中显示，创建视图的时候报错了，

ORA-01031: insufficient privileges

原因就是为用户授予resource和connect常规角色，并不会自动授予创建视图的权限，具体可以参考(http://blog.csdn.net/bisal/article/details/31735185)，此时可以授予，

SQL> grant createany view to user;

Grant succeeded.

再次导入，即可以正常完成了。

对 于测试数据迁移，其实还有一点，就是是不是所有数据，都需要迁移？因为往往测试库中有一些，仅临时使用的表对象等信息，如果执行前，筛选一下真正需要的数 据，再开始执行导出导入，可能只需要迁移小部分数据，对于垃圾数据就可以直接忽略，这就是人们常说优化的极致，即不做任何事。

如果您觉得此篇文章对您有帮助，欢迎关注微信公众号：bisal的个人杂货铺，您的支持是对我最大的鼓励！共同学习，共同进步:)