从windows10g到linux11g

drop tablespace ts1 including contents and datafiles; create tablespace ts1 datafile '/oradata/orcl/ts01.dbf' size 5M; drop tablespace ts2 including contents and datafiles; create tablespace ts2 datafile '/oradata/orcl/ts02.dbf' size 5M; create user stone identified by stone default tablespace ts1 temporary tablespace temp; grant connect,resource to stone; conn stone/stone create table tb1(id number) tablespace ts1; create table tb2(id number) tablespace ts2; alter table tb1 add constraint tb1_pk primary key(id); alter table tb2 add constraint tb2_fk foreign key(id) references tb1(id);

验证表空间自包含 conn / as sysdba exec dbms_tts.transport_set_check('ts1',incl_constraints => true,full_check => true); select * from transport_set_violations;

execute dbms_tts.transport_set_check('ts2',incl_constraints => true,full_check => true); select * from transport_set_violations;

exec dbms_tts.transport_set_check('ts1,ts2',incl_constraints => true,full_check => true); select * from transport_set_violations;

9.把表空间置位只读模式 alter tablespace ts1 read only; alter tablespace ts2 read only; select tablespace_name,status from dba_tablespaces;

如果有多个表空间，那么就把多个表空间置为只读模式 10.导出表空间数据定义，只能用sys select * from nls_databasase_parameters; select userenv('language') from dual;

export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

windows: exp '/ as sysdba' file=e:\tts.dmp TABLESPACES=ts1,ts2 TRANSPORT_TABLESPACE=y

linux/unix: exp '/ as sysdba' file=/orabak/exp/tts.dmp TABLESPACES=ts1,ts2 TRANSPORT_TABLESPACE=y log=/orabak/exp/tts.log

11.把要转换的表空间数据文件ftp到目标的数据库端

12.在目标数据库上创建被传输的表空间所包含的对象的属主 select distinct owner from dba_segments where tablespace_name in ('TS1','TS2');

OWNER

STONE

sys@ORCL> DESC DBA_SYS_PRIVS Name Null? Type

GRANTEE NOT NULL VARCHAR2(30) PRIVILEGE NOT NULL VARCHAR2(40) ADMIN_OPTION VARCHAR2(3)

sys@ORCL> SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='STONE';

GRANTEE PRIVILEGE ADM

STONE UNLIMITED TABLESPACE NO

create user stone identified by stone temporary tablespace temp; grant connect,resource to stone;

13.导入表空间数据定义信息 select * from nls_databasase_parameters; select userenv('language') from dual;

export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

windows: imp '/ as sysdba' file=f:\11g\exp\tts.dmp TRANSPORT_TABLESPACE=y TABLESPACES=ts1,ts2 DATAFILES='F:\11g\exp\ts01.dbf','F:\11g\exp\ts02.dbf' linux/unix: imp '/ as sysdba ' file=/bak/stone.dmp TRANSPORT_TABLESPACE=y TABLESPACES=ts1,ts2 DATAFILES='/oradata/orcl2/test01.dbf','/oradata/orcl2/test02.dbf'

14.把导入的表空间置为读写模式 select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS

SYSTEM ONLINE
SYSAUX ONLINE UNDOTBS1 ONLINE
TEMP ONLINE USERS ONLINE
STONE ONLINE TS1 READ ONLY TS2 READ ONLY

conn stone/stone select * from tab;

将两个节点传输的表空间改为读写 conn / as sysdba alter tablespace ts1 read write; alter tablespace ts2 read write; select tablespace_name,status from dba_tablespaces; select name from v$datafile; select file_name from dba_data_files;