关于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 * fromv$transportable_platform;
PLATFORM_ID PLATFORM_NAMEENDIAN_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 UNIXLittle
4 HP-UX IA (64-bit)Big
11 Linux IA (64-bit)Little
15 HP Open VMSLittle
8 Microsoft Windows IA (64-bit)Little
PLATFORM_ID PLATFORM_NAMEENDIAN_FORMAT
----------- ---------------------------------------- --------------
9 IBM zSeries Based LinuxBig
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
2datafile '/data1/oradata/systemfile/trans01.dbf'
3size 10M;
Tablespace created.
SQL create user trans identified by trans
2default 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 selectcount(*) from test;
COUNT(*)
----------
1
SQL select * from test;
OBJECT_NAME
--------------------------------------------------------------------------------
SUBOBJECT_NAMEOBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- -------------------
CREATEDLAST_DDL_TIM TIMESTAMP STATUST G S
------------ ------------ ------------------- ------- - - -
TEST
1560415604 TABLE
27-APR-0427-APR-042004-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