oracle tts exp,TTS跨oracle版本迁移表空间

/************检查平台信息*********************************

所有tts支持平台

SELECT * FROM V$TRANSPORTABLE_PLATFORM;

当前系统平台情况

SELECT d.PLATFORM_NAME, ENDIAN_FORMAT FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d

WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;

********************************************************/

环境：

源 10.19.27.3 linux oracle 10.2.0.5.0

目标10.17.4.204  linux oracle 11.2.0.1

----------------------------------测试开始-------------------------------------------------

源端：

表空间diyindo_tts 数据文件diyindo_tts01、diyindo_tts02

用户 diyindo_tts

检查是否符合TTS要求

SQL>  EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('DIYINDO_TTS', TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

no rows selected

SQL> SELECT COUNT(*) FROM DBA_TABLES WHERE TABLESPACE_NAME='DIYINDO_TTS';

COUNT(*)

----------

2

SQL> conn diyindo_tts/diyindo_tts

Connected.

SQL> select * from tab;

TNAME       TABTYPECLUSTERID

------------------------------ ------- ----------

TTS1       TABLE

TTS2       TABLE

SQL> select count(*) from tts1;

COUNT(*)

----------

49198

SQL> conn / as sysdba

Connected.

需要传输表空间至于readonly模式

SQL> alter tablespace diyindo_tts read only;

导出并传输表空间

[oracle@fch-vm1 ~]$ exp userid=\'/ as sysdba\' tablespaces=diyindo_tts file=/data/diyindo_tts.dmp transport_tablespace=y

Export: Release 10.2.0.5.0 - Production on Wed Jan 22 12:51:56 2014

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set

Note: table data (rows) will not be exported

About to export transportable tablespace metadata...

For tablespace DIYINDO_TTS ...

. exporting cluster definitions

. exporting table definitions

. . exporting table                           TTS1

. . exporting table                           TTS2

. exporting referential integrity constraints

. exporting triggers

. end transportable tablespace metadata export

Export terminated successfully without warnings.

SQL> alter tablespace diyindo_tts read write;

传输到目标段

[oracle@fch-vm1 ~]$ scp /data/diyindo_tts.dmp 10.17.4.204:/u01

oracle@10.17.4.204's password:

diyindo_tts.dmp                     100%   16KB  16.0KB/s   00:00

[oracle@fch-vm1 ~]$ scp /data/oracle/oradata/netreporcl/diyindo_tts* 10.17.4.204:/u01/app/oradata/ORA11/

oracle@10.17.4.204's password:

diyindo_tts01.dbf                   100%   10MB  10.0MB/s   00:00

diyindo_tts02.dbf                   100%   10MB  10.0MB/s   00:00

目标：10.17.4.204

先在目标数据库创建用户diyindo

create user diyindo identified by diyindo

grant connect,resource to diyindo

使用imp导入表空间

[oracle@oracletest ORA11]$ imp userid=\'/ as sysdba\' tablespaces=diyindo_tts file=/u01/diyindo_ttsb.dmp transport_tablespace=y datafiles=/u01/app/oradata/ORA11/diyindo_tts01.dbf , /u01/app/oradata/ORA11/diyindo_tts02.dbf fromuser=diyindo_tts touser=diyindo

Import: Release 11.2.0.1.0 - Production on Wed Jan 22 13:12:35 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing 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 AL32UTF8 character set (possible charset conversion)

export client uses AL32UTF8 character set (possible charset conversion)

. importing DIYINDO_TTS's objects into DIYINDO

. . importing table                         "TTS1"

. . importing table                         "TTS2"

Import terminated successfully without warnings.

SQL> select tablespace_name ,status from dba_tablespaces;

TABLESPACE_NAME       STATUS

------------------------------ ---------

SYSTEM       ONLINE

SYSAUX       ONLINE

UNDOTBS1       ONLINE

TEMP       ONLINE

USERS       ONLINE

EXAMPLE       ONLINE

DIYINDO       ONLINE

DIYINDO_TTS       READ ONLY

8 rows selected.

SQL> alter tablespace diyindo_tts read write;

SQL> conn diyindo/diyindo

Connected.

SQL> select * from tab;

TNAME       TABTYPECLUSTERID

------------------------------ ------- ----------

TTS1       TABLE

TTS2       TABLE

SQL> SELECT COUNT(*) FROM DBA_TABLES WHERE TABLESPACE_NAME='DIYINDO_TTS';

COUNT(*)

----------

2

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24974673/viewspace-1979784/，如需转载，请注明出处，否则将追究法律责任。