内容简介：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设置为读写模式
SQL> alter tablespace emp_test read write; Tablespace altered SQL> alter tablespace orders_test read write; 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 ONLINE ORDERS_TEST ONLINE 11 rows selected
以上所述就是小编给大家介绍的《Oracle 12cr2 数据库之间跨网络传输表，分区或子分区》，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对 码农网 的支持！
猜你喜欢:
- Oracle 12C 跨网络传输数据库
- 『互联网架构』软件架构-RPC网络传输原理与实现（45）
- 一文盘点直播技术中的编解码、直播协议、网络传输与简单实现
- 一文盘点直播技术中的编解码、直播协议、网络传输与简单实现
- 网络文件传输系统 kiftd 1.0.22 发布
- kiftd 1.0.27 发布，网络文件传输系统
本站部分资源来源于网络，本站转载出于传递更多信息之目的，版权归原作者或者来源机构所有，如转载稿涉及版权问题，请联系我们。
Algorithms + Data Structures = Programs
Niklaus Wirth / Prentice Hall / 1975-11-11 / GBP 84.95
It might seem completely dated with all its examples written in the now outmoded Pascal programming language (well, unless you are one of those Delphi zealot trying to resist to the Java/.NET dominanc......一起来看看 《Algorithms + Data Structures = Programs》 这本书的介绍吧!
