Oracle使用SQL传输表空间

 ``` --源数据库 create directory src_dpump_dir as '/tmp'; create directory src_dbf_dir as '/oradata/data/SHITAN/datafile'; --目的数据库 create directory dest_dpump_dir as '/tmp'; create directory dest_dbf_dir as '/u01/oradata01/CHICAGO/datafile'; ``` 

 ``` exec dbms_tts.transport_set_check('DBS_D_JINGYU',TRUE); select * from transport_set_violations; --确定表空间设为只读 alter tablespace DBS_D_JINGYU read only; ``` 

 ``` expdp system/oracle dumpfile=EXPDP_META_SRC.DMP directory=src_dpump_dir transport_tablespaces=DBS_D_JINGYU 

Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
 /tmp/EXPDP_META_SRC.DMP

Datafiles required for transportable tablespace DBS_D_JINGYU:
 /oradata/data/SHITAN/datafile/o1_mf_dbs_d_ji_cb0dbxt0_.dbf
 Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Thu Jan 21 16:22:50 2016 elapsed 0 00:01:15

--创建源到目的数据库的数据链
 create public database link dest34 connect to system identified by oracle using 'destDB';
 select * from dual@dest34;
 --复制数据库文件
 begin
 dbms_file_transfer.put_file
 ('src_dbf_dir','o1_mf_dbs_d_ji_cb0dbxt0_.dbf',
 'dest_dbf_dir','o1_mf_dbs_d_ji_cb0dbxt0_.dbf',	
 'dest34');
 end;
 /
 --复制转储文件
 begin
 dbms_file_transfer.put_file
 ('src_dpump_dir','EXPDP_META_SRC.DMP',
 'dest_dpump_dir','EXPDP_META_SRC.DMP',	
 'dest34');
 end;
 /

--需要先创建用户jingyu并赋权
 create user jingyu identified by jingyu ;
 grant dba to jingyu;
 --导入表空间
 impdp system/oracle dumpfile=EXPDP_META_SRC.DMP directory=dest_dpump_dir transport_datafiles=/u01/oradata01/CHICAGO/datafile/o1_mf_dbs_d_ji_cb0dbxt0_.dbf
 --更改用户jingyu的默认表空间和临时表空间（可选，建议操作）
 create temporary tablespace temp_jingyu tempfile size 30M autoextend off;
 alter user jingyu TEMPORARY TABLESPACE temp_jingyu
 DEFAULT TABLESPACE dbs_d_jingyu
 QUOTA UNLIMITED ON dbs_d_jingyu;

--分别检查表空间状态
 select TABLESPACE_NAME, STATUS from dba_tablespaces;
 --设置表空间为读写状态
 alter tablespace DBS_D_JINGYU read write;