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

单位有一套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.

Grant succeeded.

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

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

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