|
select *fromV$transportable_Platform --ENDIAN_FORMAT格式要一致
先查看两个平台的endian_format
SQL> conn sys/oracle@orclutf as sysdba
已连接。
SQL> SELECT d.PLATFORM_NAME, ENDIAN_FORMAT FROMV$TRANSPORTABLE_PLATFORM tp, V$
ATABASE d WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;
PLATFORM_NAME
-------------------------------------------------------------------------------
ENDIAN_FORMAT
----------------------------
Microsoft Windows IA (32-bit)
Little
SQL> conn sys/oracle@ocp as sysdba
已连接。
SQL> SELECT d.PLATFORM_NAME, ENDIAN_FORMAT FROMV$TRANSPORTABLE_PLATFORM tp, V$D
ATABASE d WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;
PLATFORM_NAME
--------------------------------------------------------------------------------
ENDIAN_FORMAT
----------------------------
Linux 64-bit for AMD
Little
创建测试环境
1、建立表空间
sys@OCP> CREATE TABLESPACE test_transport
2 datafile'/u01/app/oracle/oradata/OCP/datafile/test_transport.dbf' size 1M autoextend onnext 1M;
2、创建用户
CREATE USER test_user IDENTIFIED BY test_user
DEFAULT TABLESPACE test_transport TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON test_transport
/
3、授权用户
sys@OCP> GRANT CREATE SESSION,CREATE TABLE TO test_user;
4、创建表
1 CREATE TABLE test_tab(
2 id NUMBER,
3 descriprion VARCHAR2(30),
4 CONSTRAINT pk_test_tab PRIMARY KEY (id)
5* )
test_user@OCP> /
5、插入数据
1 INSERT /*+ APPEND */ INTO test_tab (id,descriprion)
2 SELECT level,
3 'Description for ' || level
4 FROM dual
5* CONNECT BY level <=10000
test_user@OCP> /
test_user@OCP> commit;
6、源数据库端检查：
检查表空间里的对象是否自包含
idle> conn / as sysdba
Connected.
sys@OCP> EXEC SYS.DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'TEST_TRANSPORT', incl_constraints =>TRUE);
PL/SQL procedure successfully completed.
7、查询是否有违反
sys@OCP> SELECT * FROM transport_set_violations;
no rows selected
8、检查成功后，切换表空间为只读
sys@OCP> ALTER TABLESPACE test_transport READ ONLY;
Tablespace altered.
9、先进行文件格式转换再导出表空间的源数据，源数据在system表空间上，使用expdp必须创建目录对象
RMAN> convert tablespace test_transport to platform 'MicrosoftWindows IA (32-bit)' format '/home/oracle/tbs_transport.dbf' ;
sys@OCP> CREATE OR REPLACE DIRECTORY dir1 as'/u01/app/oracle/ocp/directories/dir1';
Directory created.
sys@OCP> GRANT READ,WRITE ON DIRECTORY dir1 to system;
现在开始导出表空间的源数据
[oracle@station10 dir1]$ expdp userid=system/oracle directory=dir1transport_tablespaces=test_transport dumpfile=test_transport1.dmplogfile=test_transport.log
Export: Release 10.2.0.1.0 - 64bit Production on Saturday, 03August, 2013 14:59:49
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting"SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": userid=system/******** directory=dir1transport_tablespaces=test_transport dumpfile=test_transport1.dmplogfile=test_transport.log
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table"SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfullyloaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
/u01/app/oracle/ocp/directories/dir1/test_transport.dmp
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01"successfully completed at 15:00:18
如果你使用的是exp那么就不用目录对象了直接使用下面语句就可以
exp userid='system/oracle as sysdba' transport_tablespace=y tablespaces=test_transport file=test_data.dmp log=test_data_exp.log
10、目标数据库：
因为要传输的表空间属于test_user用户所有所以，目标数据库必须也要有存在一个叫做test_user的用户。
QL> conn sys/oracle@orclutf
SQL> conn sys/oracle@orclutf as sysdba
SQL> CRATE USER test_user identified by test_user;
SQL> CREATE USER test_user identified by test_user;
SQL> GRANT CREATE SESSION, CREATE TABLE TO test_user;
11、创建目录对象授权，将文件传到目标数据库的目录对象下
SQL> CREATE OR REPLACE DIRECTORY dir1 as 'c:\ dir2';
目录已创建。
SQL> GRANT READ,WRITE ON directory dir1 to system;
授权成功。
11开始导入表空间
导入成功后检查数据
SQL> conn test_user/test_user@orclutf
已连接。
SQL> select count(*) from test_tab;
COUNT(*)
----------
10000
12、将目标数据库和源数据库的表空间改为读写
查询表空间是否是导入的可以查看dba_tablespaces plugged_in 字段
SQL> select tablespace_name,plugged_in from dba_tablespaces;
TABLESPACE_NAME PLUGGE
------------------------------------------------------------ ------
SYSTEM NO
UNDOTBS1 NO
SYSAUX NO
TEMP NO
USERS NO
TEST_TRANSPORT YES
SQL> conn sys/oracle@orclutf as sysdba
已连接。
SQL> select status from dba_tablespaces;
STATUS
------------------
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
READ ONLY
已选择6行。
SQL> alter tablespace test_transport read write;
表空间已更改。
sys@OCP> alter tablespace test_transport read write;
Tablespace altered.