当前位置导航:炫浪网>>网络学院>>编程开发>>Oracle教程

关于Oracle10g跨平台传输表空间


  1.准备工作:
  查询源数据库平台信息
  
  SQL> col platform_name for a40
  SQL> SELECT d.PLATFORM_NAME, ENDIAN_FORMAT
  2 FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
  3 WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;
  
  PLATFORM_NAME ENDIAN_FORMAT
  ---------------------------------------- --------------
  Solaris[tm] OE (64-bit) Big
  
  查询目标数据库平台信息
  
  SQL> col platform_name for a40
  SQL> SELECT d.PLATFORM_NAME, ENDIAN_FORMAT
  2 FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
  3 WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;
  
  PLATFORM_NAME ENDIAN_FORMAT
  ---------------------------------------- --------------
  Microsoft Windows IA (32-bit) Little
  
  查询Oracle10g支持的平台转换
  
  代码:--------------------------------------------------------------------------------
  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
  
  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 AMD     Little
  
  2.创建一个独立的自包含表空间
  用于测试.
  
  代码:--------------------------------------------------------------------------------
  $ sqlplus "/ as sysdba"
  
  SQL*Plus: Release 10.1.0.2.0 - Production on Tue Apr 27 14:04:08 2004
  
  Copyright (c) 1982, 2004, Oracle. All rights reserved.
  
  Connected to:
  Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
  With the Partitioning, OLAP and Data Mining options
  
  SQL> select name from v$datafile;
  
  NAME
  --------------------------------------------------------------------------------
  /opt/oracle/oradata/eygle/system01.dbf
  /opt/oracle/oradata/eygle/undotbs01.dbf
  /opt/oracle/oradata/eygle/sysaux01.dbf
  /opt/oracle/oradata/eygle/users01.dbf
  /data1/oradata/systemfile/eygle01.dbf
  /opt/oracle/oradata/eygle/EYGLE/datafile/o1_mf_test_03xv34ny_.dbf
  /opt/oracle/oradata/eygle/EYGLE/datafile/o1_mf_itpub_03xv5g66_.dbf
  
  7 rows selected.
  
  SQL> create tablespace trans
   2 datafile '/data1/oradata/systemfile/trans01.dbf'
   3 size 10M;
  
  Tablespace created.
  
  SQL> create user trans identified by trans
   2 default tablespace trans;
  
  User created.
  
  SQL> grant connect,resource to trans;
  
  Grant succeeded.
  
  SQL> connect trans/trans
  Connected.
  
  SQL> create table test as select * from user_objects;
  
  Table created.
  
  SQL> select count(*) from test;
  
   COUNT(*)
  ----------
       1
  
  SQL> select * from test;
  
  OBJECT_NAME
  --------------------------------------------------------------------------------
  SUBOBJECT_NAME         OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
  ------------------------------ ---------- -------------- -------------------
  CREATED   LAST_DDL_TIM TIMESTAMP      STATUS T G S
  ------------ ------------ ------------------- ------- - - -
  TEST
                    15604     15604 TABLE
  27-APR-04  27-APR-04  2004-04-27:14:05:42 VALID  N N N
  
  
  SQL> exit
  Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
  With the Partitioning, OLAP and Dat
  
  3.导出要传输的表空间
  $ pwd
  /opt/oracle
  $ cd dpdata
  $ ls
  $ expdp eygle/eygle dumpfile=trans.dmp directory=dpdata transport_tablespace=trans
  LRM-00101: unknown parameter name 'transport_tablespace'
  
  $ expdp eygle/eygle dumpfile=trans.dmp directory=dpdata TRANSPORT_TABLESPACES=trans
  
  Export: Release 10.1.0.2.0 - 64bit Production on Tuesday, 27 April, 2004 14:07
  
  Copyright (c) 2003, Oracle. All rights reserved.
  
  Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
  With the Partitioning, OLAP and Data Mining options
  Starting "EYGLE"."SYS_EXPORT_TRANSPORTABLE_01": eygle/******** dumpfile=trans.dmp directory=dpdata TRANSPORT_TABLESPACES=trans
  ORA-39123: Data Pump transportable tablespace job aborted
  ORA-29335: tablespace 'TRANS' is not read only
  
  Job "EYGLE"."SYS_EXPORT_TRANSPORTABLE_01" stopped due to fatal error at 14:08
  
  注意:传输表空间必须置为只读状态
  
  $ sqlplus "/ as sysdba"
  
  SQL*Plus: Release 10.1.0.2.0 - Production on Tue Apr 27 14:08:13 2004
  
  Copyright (c) 1982, 2004, Oracle. All rights reserved.
  
  Connected to:
  Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
  With the Partitioning, OLAP and Data Mining options
  
  SQL> alter tablespace trans read only;
  
  Tablespace altered.
  
  SQL> exit
  Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
  With the Partitioning, OLAP and Data Mining options
  
  $ expdp eygle/eygle dumpfile=trans.dmp directory=dpdata TRANSPORT_TABLESPACES=trans
  
  Export: Release 10.1.0.2.0 - 64bit Production on Tuesday, 27 April, 2004 14:08
  
  Copyright (c) 2003, Oracle. All rights reserved.
  
  Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
  With the Partitioning, OLAP and Data Mining options
  Starting "EYGLE"."SYS_EXPORT_TRANSPORTABLE_01": eygle/******** dumpfile=trans.dmp directory=dpdata TRANSPORT_TABLESPACES=trans
  Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
  Processing object type TRANSPORTABLE_EXPORT/TABLE
  Processing object type TRANSPORTABLE_EXPORT/TTE_POSTINST/PLUGTS_BLK
  Master table "EYGLE"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
  ******************************************************************************
  Dump file set for EYGLE.SYS_EXPORT_TRANSPORTABLE_01 is:
  /opt/oracle/dpdata/trans.dmp
  Job "EYGLE"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 14:09
  
  4.使用rman转换文件格式
  $ rman target /
  
  Recovery Manager: Release 10.1.0.2.0 - 64bit Production
  
  Copyright (c) 1995, 2004, Oracle. All rights reserved.
  
  connected to target database: EYGLE (DBID=1337390772)
  
  RMAN> convert tablespace trans
  2> to platform 'Microsoft Windows IA (32-bit)'
  3
相关内容
赞助商链接