前言:ORACLE 11G EM控制台提供的传输表空间,是指在到另一个数据库上拷贝一份源数据库表空间,而非在同个数据库中复制表空间。现在大致步骤记录下并简单说明:
准备工作:
设置此次会话字符集: export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK .
将源数据库的表空间设置为只读模式: alter tablespace sandbox2 read only;
1.exp "'/ as sysdba'" file=/tmp/exp/exp_sandbox.dmp log=/tmp/exp/exp.log owner=sandbox2
2.create tablespace sandbox3 datafile '... / sandbox3_01.dbf' size 100m;
3.create user sandbox3 identified by sandbox3 default tablespace sandbox3 temporary tablespace temp;
4.grant connect,resource to sandbox3; //resource限额为unlimited
5.revoke unlimited tablespace from sandbox3; //回收user sandbox3的unlimited tablespace限额(对所有表空间)
6.alter user sandbox3 quota 0 on sandbox2; //对sandbox2的限额为0 (没有权限)
7.alter user sandbox3 quota unlimited on sandbox3; //对sandbox3的限额为无限制
-------------这几部操作的关键是通过用户的关系,把用户的默认表空间设置修改,重新配置。这样 sandbox3用户 只有对sandbox3表空间的权限,而对sandbox2为0.预防了冲突
8.imp "'/ as sysdba'" file=/tmp/exp/exp_sandbox.dmp fromuser=sandbox2 touser=sandbox3
--------通过fromuser 和touser 将sandbox2用户下的表空间 赋给sandbox3