Oracle uses SQL to transport table space

SOURCE Environment: RHEL 6.4 + Oracle 11.2.0.4
Purpose Environment: RHEL 6.4 + Oracle 11.2.0.4 DG Dual Machine
Requirement: Use SQL to transport table space Dbs_d_jingyu from the source environment to the destination environment.

• 1. Create a Directory
• 2. Check the consistency of the table space itself
• 3.EXPDP Exporting Table Space metadata
• 4. dump files and data files are copied to the destination database server
• 5. On the destination database, IMPDP import table space
• 6. Table space is set to read and write (source and destination databases)

1. Create a Directory

--源数据库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‘;

2. Check the consistency of the table space itself

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

3.EXPDP Exporting Table Space metadata

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_.dbfJob "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Thu Jan 21 16:22:50 2016 elapsed 0 00:01:15

4. dump files and data files are copied to the destination database server

--创建源到目的数据库的数据链create public database link dest34 connect to system identified by oracle using ‘destDB‘;select * from [email protected];--复制数据库文件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;/

5. On the destination database, IMPDP import table space

--需要先创建用户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;

6. Table space is set to read and write (source and destination databases)

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

Oracle uses SQL to transport table space