10g同字节跨平台表空间传输

此次是将10glinux 32位的库传输到10gwindows平台，10g的同字节跨平台传输和相同平台基本一样，可直接导入。

1.平台字节检查

SQL> select d.platform_name,endian_format from V$transportable_platform tp,V$database d

  where tp.platform_name=d.platform_name;

 

PLATFORM_NAME                  ENDIAN_FORMAT

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

Linux IA (32-bit)              Little

 

SQL> select d.platform_name,endian_format from V$transportable_platform tp,V$database d

  2    where tp.platform_name=d.platform_name;

 

PLATFORM_NAME

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

ENDIAN_FORMAT

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

Microsoft Windows IA (32-bit)

Little

 

 

 

SQL> 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

          9 IBM zSeries Based Linux                            Big

         13 Linux 64-bit for AMD                               Little

         16 Apple Mac OS                                       Big

         12 Microsoft Windows 64-bit for AMD                   Little

         17 Solaris Operating System (x86)                     Little

         18 IBM Power Based Linux                              Big

2.表空间导出

必须设置为read only

SQL> alter tablespace users read only;

TSQL> alter tablespace tan read only;

在源库导出元数据

 [oracle@dbserver1 ~]$ exp \'sys/system as sysdba\' tablespaces=users,tan

transport_tablespace=y file=exp_userstan.dmp;

Export terminated successfully with warnings.

在目标库创建用户

SQL> create user scott identified by scott;

SQL> create user tan identified by tan;

拷贝数据文件和dmp文件

3.表空间导入：

c:\oracle>imp 'sys/system as sysdba' transport_tablespace=y file=exp_userstan.dmp

log=imp.log tablespaces=users,tan

datafiles="c:\oracle\tan02\users01.dbf, c:\oracle\tan02\tan01.dbf"

Import: Release 10.2.0.3.0 - Production on Fri May 4 10:21:12 2012

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.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 WE8ISO8859P1 character set and AL16UTF16 NCHAR character set

export client uses US7ASCII character set (possible charset conversion)

. importing SYS's objects into SYS

. importing SYS's objects into SYS

. importing SCOTT's objects into SCOTT

. . importing table                         "DEPT"

. . importing table                          "EMP"

. . importing table                        "BONUS"

. . importing table                     "SALGRADE"

. importing TAN's objects into TAN

. . importing table                          "TAN"

. importing SCOTT's objects into SCOTT

. importing SYS's objects into SYS

Import terminated successfully without warnings.

4.测试

SQL> grant connect,resource,dba to tan;

SQL> conn tan/tan

Error accessing PRODUCT_USER_PROFILE

Warning:  Product user profile information not loaded!

You may need to run PUPBLD.SQL as SYSTEM

Connected.

SQL>

SQL> conn system/system

Connected.

SQL> @?/sqlplus/admin/pupbld.sql;

SQL> select count(*) from tan;

  COUNT(*)

----------

        22