此次是将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