transport tablespace将一个表空间下的数据移到另一个表空间

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

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

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;