表空间传输

表空间传输（速度极快）

表空间传输是把一个数据库上的格式数据文件附加到另一个数据库中，而不是把数据导出成dmp文件，这在有些时候是非常管用的，因为传输入表空间移动数据就像复制文件一样块;

 

Example

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

 

到此表空间传输已完成;

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

转载于:http://blog.itpub.net/7862652/viewspace-709969/