传输表空间

1、检查支持的OS平台


SELECT * FROM V$TRANSPORTABLE_PLATFORM;


PLATFORM_ID PLATFORM_NAME                  ENDIAN_FORMAT


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


          1 Solaris[tm] OE (32-bit)        Big


          2 Solaris[tm] OE (64-bit)        Big


          7 Microsoft Windows IA (32-bit)  Little


         10 Linux IA (32-bit)              Little


          6 AIX-Based Systems (64-bit)     Big


          3 HP-UX (64-bit)                 Big


          5 HP Tru64 UNIX                  Little


          4 HP-UX IA (64-bit)              Big


         11 Linux IA (64-bit)              Little


         15 HP Open VMS                    Little


          8 Microsoft Windows IA (64-bit)  Little


 


PLATFORM_ID PLATFORM_NAME                  ENDIAN_FORMAT


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


          9 IBM zSeries Based Linux        Big


         13 Linux 64-bit for AMD           Little


         16 Apple Mac OS                   Big


         12 Microsoft Windows 64-bit for A Little


            MD


 


         17 Solaris Operating System (x86) Little


         18 IBM Power Based Linux          Big


 


17 rows selected.


 


2、If you are transporting 
pord
and 
colin
to a different platform, you can execute the following query on each platform. If the query returns a row, the platform supports cross-platform tablespace transport.


SELECT d.PLATFORM_NAME, ENDIAN_FORMAT
     FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
     WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;
 
The following is the query result from the source platform:


PLATFORM_NAME             ENDIAN_FORMAT
------------------------- --------------
Solaris[tm] OE (32-bit)   Big
 
The following is the result from the target platform:


PLATFORM_NAME             ENDIAN_FORMAT
------------------------- --------------
Microsoft Windows NT      Little
 


 


3、查看两边的表空间，目标库是不能传输已有的表空间


Prod


SQL> select name from v$tablespace;


 


NAME


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


SYSTEM


UNDOTBS1


SYSAUX


TEMP


USERS


OLTP


 


6 rows selected.


 


Colin


SQL> select name from v$tablespace;


 


NAME


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


SYSTEM


UNDOTBS1


SYSAUX


TEMP


USERS


 


4、传输表空间是否全是自包含（自包含是各表这间不跨表空间，包括外争键等）


SQL>  EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('OLTP',true);


 


5、查询是否有自包含，如有，则不能做表空间传输


SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;


6、在源数据上把传输表空间至为read only


SQL> alter tablespace oltp read only;


 


7、exp导出表空间，字符集一定要相同


[oracle@db ~]$ exp file=oltp.dmp TABLESPACES=oltp TRANSPORT_TABLESPACE=y  #此处不要用户名和密码，因为表空间间传输需要sys用户


[oracle@db ~]$ exp file=oltp.dmp TABLESPACES=oltp TRANSPORT_TABLESPACE=y


 


Export: Release 10.2.0.1.0 - Production on Mon Oct 25 10:21:51 2010


 


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


 


Username: sys/oracle as sysdba  #此处输入用户名密码


 


8、把刚导出来的oltp.dmp及oltp表空间下的数据文件cp 到目标数据库上


 


9、源数据库上的表空间此时改成read write（此时传输表空间已经不再有影响了）


SQL> alter tablespace oltp read write;


 


10、导入数据源到目标数据库


imp file=oltp.dmp TABLESPACES=oltp TRANSPORT_TABLESPACE=y DATAFILES='/oracle/oradata/colin/oltp01.dbf'


 


11、检查导入状态


SQL> select * from v$tablespace;


 


       TS# NAME                           INC BIG FLA ENC


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


         0 SYSTEM                         YES NO  YES


         1 UNDOTBS1                       YES NO  YES


         2 SYSAUX                         YES NO  YES


         3 TEMP                           NO  NO  YES


         4 USERS                          YES NO  YES


         5 OLTP                           YES NO  YES


 


到此表空间传输已完成;