exp oracle 表空间,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来忽略已经存在的对象。