EXP转换表空间

在使用EXP/IMP进行数据的迁移，经常会需要转换表空间的操作，简单记录一下，EXP过程碰到表空间的转换时需要注意的问题。

如果不是分区表、不包含LOB字段，且不含索引组织表的OVERFLOW段，那么可以通过下面的方法将表的迁移到目标表空间中。

将设置目标用户的默认表空间为导入的目的表空间，在默认表空间上授予QUOTA UNLLIMITED，回收改用户的UNLIMITED TABLESPACE权限：

看一个简单的例子：

SQL> CREATE TABLESPACE TESTEXP DATAFILE '/data/oradata/testdata/testexp.dbf' SIZE 100M;

表空间已创建。

SQL> CREATE USER TESTEXP IDENTIFIED BY TESTEXP DEFAULT TABLESPACE TESTEXP;

用户已创建

SQL> GRANT CONNECT, RESOURCE TO TESTEXP;

授权成功。

SQL> CONN TESTEXP/TESTEXP已连接。
SQL> CREATE TABLE T1 AS SELECT * FROM ALL_TABLES;

表已创建。

SQL> CREATE TABLE T2 TABLESPACE SYSTEM AS SELECT * FROM ALL_INDEXES;

表已创建。

SQL> CREATE TABLE T3 TABLESPACE USERS AS SELECT * FROM ALL_SYNONYMS;

表已创建。

SQL> EXIT从Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production中断开

[oracle@localhost ~]$ exp testexp/testexp file=testexp.dmp buffer=2048000

Export: Release 9.2.0.4.0 - Production on 星期一 2月 25 17:12:21 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

连接到: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
. 正在导出 pre-schema 过程对象和操作
. 正在导出用户 TESTEXP 的外部函数库名称
. 导出 PUBLIC 类型同义词
. 导出私有类型同义词
. 正在导出用户 TESTEXP 的对象类型定义即将导出 TESTEXP 的对象 ...
. 正在导出数据库链接
. 正在导出序号
. 正在导出群集定义
. 即将导出 TESTEXP 的表通过常规路径 ...
. . 正在导出表 T1 22 行被导出
. . 正在导出表 T2 31 行被导出
. . 正在导出表 T3 11713 行被导出
. 正在导出同义词
. 正在导出视图
. 正在导出存储的过程
. 正在导出运算符
. 正在导出引用完整性约束条件
. 正在导出触发器
. 正在导出索引类型
. 正在导出位图, 功能性索引和可扩展索引
. 正在导出后期表活动
. 正在导出实体化视图
. 正在导出快照日志
. 正在导出作业队列
. 正在导出刷新组和子组
. 正在导出维
. 正在导出 post-schema 过程对象和操作
. 正在导出统计在没有警告的情况下成功终止导出。

如果希望在迁移用户TESTEXP的时候将表空间转换为USERS表空间：

[oracle@localhost ~]$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on 星期一 2月 25 17:13:05 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

连接到:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> DROP USER TESTEXP CASCADE;

用户已丢弃

SQL> DROP TABLESPACE TESTEXP INCLUDING CONTENTS AND DATAFILES;

表空间已丢弃。

SQL> CREATE USER TESTEXP IDENTIFIED BY TESTEXP DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS;

用户已创建

SQL> GRANT CONNECT, RESOURCE TO TESTEXP;

授权成功。

SQL> REVOKE UNLIMITED TABLESPACE FROM TESTEXP;

撤销成功。

SQL> HOST
[oracle@localhost ~]$ imp testexp/testexp file=testexp.dmp full=y

Import: Release 9.2.0.4.0 - Production on 星期一 2月 25 17:15:04 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

连接到: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

经由常规路径导出由EXPORT:V09.02.00创建的文件已经完成ZHS16GBK字符集和AL16UTF16 NCHAR 字符集中的导入
. 正在将TESTEXP的对象导入到 TESTEXP
. . 正在导入表 "T1" 22行被导入
. . 正在导入表 "T2" 31行被导入
. . 正在导入表 "T3" 11713行被导入成功终止导入，但出现警告。
[oracle@localhost ~]$ exit
exit

SQL> SELECT TABLE_NAME, TABLESPACE_NAME
2 FROM DBA_TABLES
3 WHERE OWNER = 'TESTEXP';

TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
T1 USERS
T2 USERS
T3 USERS

根据前面描述的三个步骤，设置目标用户的默认表空间，设置默认表空间上的QUOTA，回收目标用户的UNLIMITED TABLESPACE权限，来达到向目标表空间迁移的目的。

如果没有回收UNLIMITED TABLESPACE权限，则用户有对所有的表空间具有权限，那么导入的时候，如果同名表空间存在，则直接在这个表空间上建立表，而不会建立在目标用户的默认表空间上，如下面的例子，所有的操作完全一致，只是没有回收UNLIMITED TABLESPACE权限：

SQL> DROP USER TESTEXP CASCADE;

用户已丢弃

SQL> CREATE USER TESTEXP IDENTIFIED BY TESTEXP DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS;

用户已创建

SQL> GRANT CONNECT, RESOURCE TO TESTEXP;

授权成功。

SQL> HOST
[oracle@localhost ~]$ imp testexp/testexp file=testexp.dmp full=y

Import: Release 9.2.0.4.0 - Production on 星期一 2月 25 17:20:10 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

连接到: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

经由常规路径导出由EXPORT:V09.02.00创建的文件已经完成ZHS16GBK字符集和AL16UTF16 NCHAR 字符集中的导入
. 正在将TESTEXP的对象导入到 TESTEXP
. . 正在导入表 "T1" 22行被导入
. . 正在导入表 "T2" 31行被导入
. . 正在导入表 "T3" 11713行被导入成功终止导入，但出现警告。
[oracle@localhost ~]$ exit
exit

SQL> SELECT TABLE_NAME, TABLESPACE_NAME
2 FROM DBA_TABLES
3 WHERE OWNER = 'TESTEXP';

TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
T1 USERS
T2 SYSTEM
T3 USERS

由于T1原来的表空间不存在，因此导入的时候Oracle选择了默认表空间USERS，对于T3本身就是USERS表空间，因此导入时仍然选择USERS表空间，而对于T2，表空间SYSTEM在目标库存在，且目标用户TESTEXP有SYSTEM表空间上的QUOTA，那么在导入的时候Oracle直接在SYSTEM表空间上建立了T2表。

上面描述的方法只对非分区表、不包含LOB字段以及不包含索引组织表的OVERFLOW段有效，对于包含这些情况的表使用这种方法只能转换表的属性，而无法转换分区段、LOB段以及OVERFLOW段的表空间设置。

如果希望上述对象也可以顺利晚上表空间的转换，最好的方法是通过预先建立这些对象，并在IMP导入的时候指定IGNORE=Y来忽略已经存在的对象。