ORACLE传输表空间

从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