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

最新推荐文章于 2024-04-19 17:29:33 发布

iteye_4537

最新推荐文章于 2024-04-19 17:29:33 发布

阅读量364

点赞数

文章标签：数据库

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、完成