在使用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来忽略已经存在的对象。