表空间传输

一：环境说明:
1:源数据库：
　　window2003 (32 bit)+oracle11g
　　实例:goldrid

2:目标数据库：
　　linux as5(32 bit)+oracle11g
　　实例:school

二：操作步骤

1:查看是否是相同的字节顺序
select * from v$transportable_platform order by platform_id;

2:源数据库表空间的建立;
CREATE TABLESPACE ts_trans
LOGGING
DATAFILE
'd:oracleoradatagoldbridts_trans_001.dbf' SIZE 10M,
'd:oracleoradatagoldbridts_trans_002.dbf' SIZE 10M
REUSE DEFAULT STORAGE ( INITIAL 64K NEXT 32K MINEXTENTS 1 MAXEXTENTS 505
PCTINCREASE 0 );

create user trans identified by trans;
grant resource,connect,dba to trans;
grant create table to trans;
alter user trans default tablespace ts_trans;
GRANT UNLIMITED TABLESPACE TO trans;
alter user trans temporary tablespace temp;

grant select on teachplan.phy_cc to trans;
3:生成测试数据
conn trans/trans;
create table phy_cc as select * from teachplan.phy_cc ;
4:表空间ts_trans设置成只读
EXEC SYS.DBMS_TTS.TRANSPORT_SET_CHECK('TEST', TRUE);
alter tablespace ts_trans read only;

5:把文件ts_trans_001.dbf,ts_trans_002.dbf从源拷贝到目标数据库的主机上
6:exp tablespaces=ts_trans transport_tablespace=y file=exp_ts_trans.dmp
exp tablespaces=users transport_tablespace=y file=exp_ts_users.dmp

expdp system/xxxxx@goldbrid DUMPFILE=exp_ts_trans.dmp DIRECTORY=expdp TRANSPORT_TABLESPACES=ts_trans

2003下面的脚本运行后，文件名全变成大写的了

impdp system/xxxxx@school DUMPFILE=EXP_TS_TRANS.DMP DIRECTORY=EXPDP TRANSPORT_DATAFILES=/home/oracle/TS_TRANS_001.DBF,/home/oracle/TS_TRANS_002.DBF

7:导入表空间
su - oracle
export ORACLE_SID=school
cd 文件所在的目录

imp tablespaces=ts_rans transport_tablespace=y file=exp_ts_trans.dmp datafiles='ts_trans_001.dbf','ts_trans_002.dbf';
8:把目标数据库表空间设置成正常状态
alter tablespace ts_rans read write;
9:验证表空间是否可读写
select * from phy_cc where rownum<10;
create table phy_cc_new as select * from phy_cc;

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/67798/viewspace-1017975/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/67798/viewspace-1017975/