ApsaraDB RDS：同步更新Oracle資料庫（oracle_fdw）

注意事項

• 如果需要執行UPDATE和DELETE操作，需要在建立外部表格時為主鍵列設定key參數，詳情請參見建立外部表格。

• 外部表格定義的列資料類型必須是oracle_fdw可以識別並可以轉換的，oracle_fdw外掛程式對於資料類型的轉換規則請參見Data types。

• WHERE子句和ORDER BY子句支援計算下推，即oracle_fdw會將子句發送給Oracle進行計算。

• JOIN操作支援計算下推，但是有以下注意事項：

  • 兩個表必須被定義在相同的映射中。

  • 三個及以上的JOIN操作不支援下推。

  • JOIN操作必須包含在SELECT操作中。

  • 沒有JOIN條件的CROSS JOIN操作不支援下推。

  • 如果JOIN子句被下推，ORDER BY子句將不會被下推。

• 安裝postgis外掛程式後，oracle_fdw外掛程式支援以下空間資料類型：

  • POINT

  • LINE

  • POLYGON

  • MULTIPOINT

  • MULTILINE

  • MULTIPOLYGON

安裝和卸載外掛程式

安裝外掛程式

CREATE EXTENSION oracle_fdw;

卸載外掛程式

DROP EXTENSION oracle_fdw;

操作步驟

1. 建立Oracle資料庫映射。有如下兩種命令：

   • CREATE SERVER <SERVER名稱>
     FOREIGN DATA WRAPPER oracle_fdw
     OPTIONS (dbserver '//<Oracle資料庫的內網串連地址>:<Oracle資料庫的內網串連連接埠>/<資料庫名>');

   • CREATE SERVER oradb
     FOREIGN DATA WRAPPER oracle_fdw
     OPTIONS (host '<Oracle資料庫的內網串連地址>', port '<Oracle資料庫的內網串連連接埠>', dbname '<資料庫名>');

2. 建立使用者映射。命令如下：

   CREATE USER MAPPING
   FOR <PostgreSQL使用者名稱> SERVER <映射名>
   OPTIONS (user '<Oracle資料庫使用者名稱>', password '<Oracle資料庫使用者密碼>');

   說明

   如果不在PostgreSQL資料庫中儲存Oracle使用者憑證，可以設定user為空白字串，然後提供必要的外部授權。

   樣本

   CREATE USER MAPPING
   FOR pguser SERVER oradb
   OPTIONS (user 'orauser', password 'orapwd');

3. 建立Oracle的外部表格。樣本如下：

   CREATE FOREIGN TABLE oratab (
             id        integer OPTIONS (key 'true')  NOT NULL,
             text      character varying(30),
             floating  double precision  NOT NULL
          ) SERVER oradb OPTIONS (table 'ORATAB',
                                  schema 'ORAUSER',
                                  max_long '32767',
                                  readonly 'false',
                                  sample_percent '100',
                                  prefetch '200');

   說明

   外部表格的結構需要和Oracle中的映射表結構保持一致。

   OPTIONS內的參數說明如下。

   參數	說明
   key	是否設定對應的列為主鍵，取值為true或false，預設值為false。如果要執行UPDATE和DELETE操作，必須將所有主鍵列設定為true。
   table	表名，一般是大寫，必填參數。可以使用Oracle的SQL來定義table變數的值，例如：OPTIONS (table '(SELECT col FROM tab WHERE val = ''string'')')，此時不要使用schema參數。
   schema	一般是Oracle使用者名稱，保持大寫，用來訪問不屬於當前串連使用者的表。
   max_long	限制Oracle表中LONG、LONG RAW、XMLTYPE類型列的最大長度，取值範圍是1~1073741823，預設值是32767。
   readonly	限制Oracle表為唯讀，不允許INSERT、UPDATE、DELETE操作。
   sample_percent	設定隨機播放Oracle表資料的比例，用於PostgreSQL表統計資訊，取值範圍是0.000001~100，預設值是100。
   prefetch	外部表格掃描時，PostgreSQL和Oracle資料表之間一次性傳輸的行數，取值範圍是0~1024，預設值是200，0代表取消prefetch功能。

完成以上步驟即可通過操作外部表格來實現對Oracle表的操作。支援DELETE、INSERT、UPDATE、SELECT等基本操作，支援匯入外部表格定義的操作，命令如下：

IMPORT FOREIGN SCHEMA <ora_schema_name>
FROM SERVER <server_name>
INTO <schema_name>
OPTIONS (case 'lower');