从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;
转载于:https://blog.51cto.com/lyzbg/2090818