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三步。