http://blog.csdn.net/macliukaijie/article/details/8308643
1、创建两个表空间
SQL> create tablespace test1 datafile '/opt/test1.dbf' size 10m;
Tablespace created
SQL> create tablespace test2 datafile '/opt/test2.dbf' size 10m;
Tablespace created
2、创建两个用户
SQL> create user test1 identified by test1 default tablespace test1;
User created
SQL> create user test2 identified by test2 default tablespace test2;
User created
3.给权限
SQL> grant connect,resource to test1;
Grant succeeded
SQL> grant connect,resource to test2;
Grant succeeded
4.在test1表空间里建表
conn test1/test1
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as test1
SQL> create table t(i number) tablespace test1;
Table created
SQL> begin
2 for i in 1..10000 loop
3 insert into t values (i);
4 end loop;
5 commit;
6 end ;
7
8 /
PL/SQL procedure successfully completed
5.把表空间设置成只读模式
SQL> alter tablespace test1 read only;
Tablespace altered
6、使用transport tablespace导出数据
SQL> host exp transport_tablespace=y tablespaces=test1 file=/opt/test1.dmp
Export: Release 10.2.0.1.0 - Production on Mon Dec 17 04:44:11 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Username: sys
Password:
EXP-00056: ORACLE error 28009 encountered
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER
Username: sys as sysdba
Password:
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
For tablespace TEST1 ...
. exporting cluster definitions
. exporting table definitions
. . exporting table T
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.
7.将数据移到test2用户下
SQL> host imp transport_tablespace=y file=/opt/test1.dmp fromuser=test1 touser=test2 datafiles='/opt/test1.dbf';
Import: Release 10.2.0.1.0 - Production on Mon Dec 17 04:50:24 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Username: sys as sysdba
Password:
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path
About to import transportable tablespace(s) metadata...
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing TEST1's objects into TEST2
. . importing table "T"
Import terminated successfully without warnings.
8.查询test2用户下的数据
SQL> select count(*) from t;
COUNT(*)
----------
10000
9、完成
-----------------------未验证------------------
------------------另------------------------------------------------------------------------
ORACLE数据库设置表空间为只读/写模式
了保证表空间数据完整性，比如：进行数据库备份与还原操作、历史数据的完整性保护等情况下，可以将表空间设置到只读模式。只读表空间中的表不能进行INSERT、UPDATE、DELETE等操作，但是可以删除数据库对象。
需要dba权限的用户登录才可以进行一下操作：
SQL> select tablespace_name,status from dba_tablespaces;
SQL> alter tablespace demo read only;
SQL> select tablespace_name,status from dba_tablespaces;
SQL> alter tablespace demo read write;
SQL> select tablespace_name,status from dba_tablespaces;
