Oracle 12cr2 資料庫之間跨網路傳輸表，分割槽或子分割槽

eric0435發表於2017-06-06
Oracle資料庫

為了跨網路傳輸表，可以在執行匯入操作時使用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設定為讀寫模式


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26015009/viewspace-2140348/，如需轉載，請註明出處，否則將追究法律責任。

相關文章