oracle 中导入数据(含Lob字段)到不同表空间的问题

 

我以前一直使用 users 表空间作为默认空间 , 最近想把现有用户的数据都转到一个新建的表空间中 , 可是发现 Imp 后所有表还是在 users 表空间里面 , 查阅了一些资料 , 说是 Oracle 自己没有提供分配表空间的功能 ,Exp 的时候把表的 storage 选项也导出了 ,Imp 按照原表的建表语句 , 默认表空间是原来的 users. 要想导入到新的表空间 , 提供了几种方法 , 包括先在新表空间建表 , 然后再 imp 就可以了 , 还有指定 user 的表空间分配额度（ quota ），以及先导入然后更改表的表空间的办法。

我用的表多，先试了更改 user 的 quota 选项的方法，决定这是最方便而且彻底解决问题的办法。
过程如下：

 

先建立一个新的表空间 tn 和用户 test1
用 system 登录 sqlplus
SQL> create tablespace tn datafile '/u01/oracle/oradata/gdfaooa/tn01.dbf' size 10M autoextend on next 5M maxsize 2048M default storage(initial 64K next 1M pctincrease 0) extent management local uniform size 64K SEGMENT SPACE MANAGEMENT AUTO;
SQL> create user test1 identified by manager default tablespace tn temporary tablespace temp profile default;
SQL> grant connect to test1;
SQL> grant resource to test1;
SQL> alter user test1 quota unlimited on tn;
SQL> alter user test1 quota unlimited on users;
SQL> alter user test1 quota unlimited on system;
SQL> alter user test1 quota unlimited on tools;

如果已经建立用户，应该收回目的用户的 "UNLIMITED TABLESPACE" 权限：

revoke unlimited tablespace from username;

建表空间的参数上 , 值得慢慢琢磨一下 ,default storage 可以不要 , 最主要是 dbf 的 size 与 下一个 extend 的大小问题 , 如何保证节省空间 .

Itpub 上有人说 resource 权限导致 oracle 默认用户在系统表空间的 quota 是 unlimited, 这个我还要确定一下。

现在执行 imp

imp test1/manager file=test.dmp log=test1.log fromuser=htest touser=test1 ignore=y

中间报了很多错，也显示一些表导入成功。看 test1.log 查错误，发现还是报在 users 表空间 quota 不够。我奇怪了半天，怎么有些表可以导入到 dn 表空间，有些却不行那，后来仔细看，发现错的表都是含 LOB 字段的。

原来是 lob 字段不能导入到新表空间，怎么解决呢？查资料、问人，都说 lob 字段特殊，在建表存储的时候系统就特殊处理 lob 字段的存储，所以导入导出的时候不按通常表来处理，真够郁闷的。

现在只好想别的办法了，表很多，挨个在新表空间建表多麻烦啊，还要找建表的 sql 。可是先导入再用工具改或者用 move 命令，都一样，一百多个 lob 字段的表啊 !! 还有 toad 只找到试用版，不知道改变表的表空间的功能可不可以用。

后来想起自己常用的 PLSQL Developer 是不是也有这样的功能，就去看了看，好像没有改变表空间的功能，但是有导出当前用户所有 Object 的功能，生成的是所有 object （表和视图）的 create 的 sql 文件。

sql 文件打开进行编辑建表的 sql 语句都指明了存储的表空间，全部把 users 换成 tn ，这样用 test1 登录进去执行 sql 文件就可以建立所有表和视图的表结构了，然后导入 dmp 数据就没有问题了。

然后我进去查询，发现一切 OK 啦，所有的表都存储在 tn 表空间上。不过 tn 表空间的数据文件也增大了很多，本来 70 多 M 的 dmp 导入进去，数据文件从 10M 已经增长到 300 多 M 了，看来还是要好好琢磨一下表空间的参数了

总结过程：
1. 建立新的表空间
2. 建立新用户，默认表空间为刚建立的新的表空间
3. 设定用户权限， quota 还是要设定一下。
4. 从原用户导出数据： exp username/password file=filename owner=username rows=y grants=y
5. 从原用户那里，用 PLSQL Developer （其他的工具也可以）导出原用户的所有 object ，生成 sql 文件。
6. 编辑 sql 文件，替换所有的旧表空间为新表空间。
7. 用新用户登录，并执行 sql 文件，生成表结构
8. 执行 Imp 导入数据： imp newusername/password file=filename log=logfile fromuser=olduser touser=newuser ignore=y
9. 查看新用户下的表的存储情况，确定完全存放在新的表空间下面：

select table_name,tablespace_name from user_tables;

如果没有lob字段的表,不用执行5、6、7三步。