Oracle 12cr2 数据库之间跨网络传输表，分区或子分区

为了跨网络传输表，可以在执行导入操作时使用network_link参数，这样导入操作将会使用数据库链路而不用先导出dump文件。其操作步骤如下:
1.选择一组表，分区或子分区。
如果是要传输分区，那么在传输表操作中可以指定一个表的分区，并且在同一操作中没有其它的表将被传输。如果在传输表操作中中只有表分区的子集被导出，那么在导入后每个分区将变成非分区表。

2.在源数据库中，将要被传输的表，分区或子分区所在表空间设置为只读模式。为了查询表所在的表空间可以查询dba_tables视图，为了查询表空间的所有文件可以查询dba_data_files视图。

3.传输表，分区或子分区所在表空间的所有数据文件到目标数据库。如果源平台与目标平台的字节编码不一样，那么可以使用以下
任何一种方法来转换数据文件。
--使用dbms_file_transfer包中的get_file或put_file过程来传输数据文件，它们会自动将数据文件转换为目标平台的字节编码。

--使用rman的convert命令来将数据文件转换为目标平台的字节编码。

4.在目标数据库上执行导入操作

5.可选操作，将源数据库中的表空间设置为读写模式

下面的例子将介绍如何使用跨网传输表，分区或子分区的方法来将一个数据库中的hr.emp_test与oe.orders_test表传输到另一个数
据库中。其中源平台与目标平台的字节编码相同。

1.先在源数据库中创建表hr.emp_test与oe.orders_test

SQL> create tablespace emp_test datafile '+DATADG/jyrac/datafile/emp_test_01.dbf' size 100M  autoextend off  extent management local segment space management auto; Tablespace created  SQL> create tablespace orders_test datafile '+DATADG/jyrac/datafile/orders_test_01.dbf' size 100M  autoextend off  extent management local segment space management auto; Tablespace created    SQL> create table hr.emp_test tablespace emp_test as select * from hr.employees; Table created  SQL> create table oe.orders_test tablespace orders_test as select * from oe.orders; Table created 

2.在目标数据库中创建数据库链路连接到源数据库

SQL> conn sys/xxzx7817600@jypdb as sysdba Connected.   SQL> create public database link jyrac_link   2    connect to jy identified by "jy"   3    using '(DESCRIPTION =   4      (ADDRESS_LIST =   5        (ADDRESS = (PROTOCOL = TCP)(HOST =10.138.130.153)(PORT = 1521))   6      )   7      (CONNECT_DATA =   8        (SERVER = DEDICATED)   9        (SERVICE_NAME =jyrac)  10      )  11    )';  Database link created. 

3.将源数据库中表hr.emp_test与oe.orders_test所在的表空间设置为只读状态

SQL> alter tablespace emp_test read only; Tablespace altered  SQL> alter tablespace orders_test read only; Tablespace altered  SQL> select tablespace_name,status from dba_tablespaces; TABLESPACE_NAME                STATUS ------------------------------ --------- SYSTEM                         ONLINE SYSAUX                         ONLINE UNDOTBS1                       ONLINE TEMP                           ONLINE USERS                          ONLINE UNDOTBS2                       ONLINE EXAMPLE                        ONLINE TEST                           ONLINE SALES_TEST                     ONLINE EMP_TEST                       READ ONLY ORDERS_TEST                    READ ONLY 11 rows selected 

4.将表空间tem_test与orders_test的所有数据文件复制到目标数据库中
在源数据库中创建目录tts_datafile（存储数据文件)

SQL> create or replace directory tts_datafile as '+datadg/jyrac/datafile/';  Directory created.  SQL> grant execute,read,write on directory tts_datafile to public;  Grant succeeded. 

在目标数据库中创建目录tts_datafile（存储数据文件)

SQL> create or replace directory tts_datafile as '+DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/';  Directory created.  SQL> grant execute,read,write on directory tts_datafile to public;  Grant succeeded.   SQL> exec dbms_file_transfer.get_file(source_directory_object =>'TTS_DATAFILE',source_file_name => 'emp_test_01.dbf',source_database =>'jyrac_link',destination_directory_object => 'TTS_DATAFILE',destination_file_name => 'emp_test_01.dbf'); PL/SQL procedure successfully completed  SQL> exec dbms_file_transfer.get_file(source_directory_object =>'TTS_DATAFILE',source_file_name => 'orders_test_01.dbf',source_database =>'jyrac_link',destination_directory_object => 'TTS_DATAFILE',destination_file_name => 'orders_test_01.dbf'); PL/SQL procedure successfully completed   ASMCMD [+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile] > ls -lt   Type      Redund  Striped  Time             Sys  Name DATAFILE  UNPROT  COARSE   JUN 06 22:00:00  N    orders_test_01.dbf => +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/FILE_TRANSFER.303.945987633 DATAFILE  UNPROT  COARSE   JUN 06 22:00:00  N    emp_test_01.dbf => +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/FILE_TRANSFER.302.945987591 DATAFILE  UNPROT  COARSE   JUN 06 22:00:00  Y    FILE_TRANSFER.303.945987633 DATAFILE  UNPROT  COARSE   JUN 06 22:00:00  Y    FILE_TRANSFER.302.945987591 DATAFILE  UNPROT  COARSE   JUN 06 19:00:00  N    sales_test_01.dbf => +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/FILE_TRANSFER.301.945975283 DATAFILE  UNPROT  COARSE   JUN 06 19:00:00  Y    FILE_TRANSFER.301.945975283 DATAFILE  UNPROT  COARSE   JUN 05 23:00:00  Y    SYSAUX.275.939167015 DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  N    users01.dbf => +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/FILE_TRANSFER.298.945620417 DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  N    test01.dbf => +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/FILE_TRANSFER.300.945620337 DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  N    example01.dbf => +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/FILE_TRANSFER.299.945620391 DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  Y    SYSTEM.274.939167015 DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  Y    FILE_TRANSFER.300.945620337 DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  Y    FILE_TRANSFER.299.945620391 DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  Y    FILE_TRANSFER.298.945620417 DATAFILE  UNPROT  COARSE   JUN 02 00:00:00  N    testtb01.dbf => +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/TESTTB.295.944828399 DATAFILE  UNPROT  COARSE   JUN 02 00:00:00  Y    UNDO_2.277.939167063 DATAFILE  UNPROT  COARSE   JUN 02 00:00:00  Y    UNDOTBS2.278.945029905 DATAFILE  UNPROT  COARSE   JUN 02 00:00:00  Y    UNDOTBS1.273.939167015 DATAFILE  UNPROT  COARSE   JUN 02 00:00:00  Y    TESTTB.295.944828399 

5.在目标数据库中执行导入操作

[oracle@jytest1 tts]$ impdp system/xxzx7817600@JYPDB_175 network_link=jyrac_link transportable=always transport_datafiles='+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/emp_test_01.dbf','+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/orders_test_01.dbf' tables=hr.emp_test,oe.orders_test logfile=imp_tables.log directory=tts_dump  Import: Release 12.2.0.1.0 - Production on Tue Jun 6 22:24:24 2017  Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.  Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production Starting "SYSTEM"."SYS_IMPORT_TABLE_01":  system/********@JYPDB_175 network_link=jyrac_link transportable=always transport_datafiles=+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/emp_test_01.dbf,+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/orders_test_01.dbf tables=hr.emp_test,oe.orders_test logfile=imp_tables.log directory=tts_dump  Processing object type TABLE_EXPORT/TABLE/PLUGTS_BLK Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/END_PLUGTS_BLK Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at Tue Jun 6 22:24:57 2017 elapsed 0 00:00:30  

6.可选操作，将源数据库中的表空间emp_test与orders_test设置为读写模式