oracle error 28009 encountered,transport tablespace将一个表空间上的数据移到另一个表空间...

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

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