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       TABTYPE CLUSTERID
------------------------------ ------- ----------
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       TABTYPE CLUSTERID
------------------------------ ------- ----------
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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24974673/viewspace-1979784/