ApsaraDB RDS:Synchronize an Oracle database with oracle_fdw

Usage notes

• To perform UPDATE and DELETE operations, you must set the key parameter for the primary key columns when you create the foreign table. For more information, see Create a foreign table.

• oracle_fdw must be able to recognize and convert the data types of columns in a foreign table. For information about the data type conversion rules for the oracle_fdw extension, see Data types.

• WHERE and ORDER BY clauses support pushdown, which means that oracle_fdw sends these clauses to the Oracle database for processing.

• JOIN operations support pushdown, but the following limits apply:

  • The tables must be defined on the same foreign server.

  • Pushdown is not supported for JOIN operations that involve three or more tables.

  • The JOIN operation must be part of a SELECT statement.

  • CROSS JOIN operations without a JOIN condition do not support pushdown.

  • If a JOIN clause is pushed down, the ORDER BY clause is not.

• After you install the PostGIS extension, the oracle_fdw extension supports the following spatial data types:

  • POINT

  • LINE

  • POLYGON

  • MULTIPOINT

  • MULTILINE

  • MULTIPOLYGON

Install and uninstall the extension

Install the extension

CREATE EXTENSION oracle_fdw;

Uninstall the extension

DROP EXTENSION oracle_fdw;

Procedure

1. Create a server to map to the Oracle database. You can use one of the following commands:

   • CREATE SERVER <server_name>
     FOREIGN DATA WRAPPER oracle_fdw
     OPTIONS (dbserver '//<oracle_internal_endpoint>:<oracle_internal_port>/<database_name>');

   • CREATE SERVER oradb
     FOREIGN DATA WRAPPER oracle_fdw
     OPTIONS (host '<oracle_internal_endpoint>', port '<oracle_internal_port>', dbname '<database_name>');

2. CREATE USER MAPPING
   FOR <postgresql_username> SERVER <server_name>
   OPTIONS (user '<oracle_username>', password '<oracle_password>');

   Note

   If you do not want to store Oracle user credentials in the PostgreSQL database, you can set the user option to an empty string and provide the required external authorization.

   Example:

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

3. 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');

   Note

   The structure of the foreign table must match the structure of the corresponding Oracle table.

   The following table describes the parameters in the OPTIONS clause.

   Parameter	Description
   key	Specifies whether the corresponding column is a primary key. Valid values are true and false. Default value: false. To perform UPDATE and DELETE operations, you must set this parameter to true for all primary key columns.
   table	The name of the table. This parameter is required and is typically in uppercase. You can also set this parameter's value to an Oracle SQL expression. Example:OPTIONS (table '(SELECT col FROM tab WHERE val = ''string'')'). If you use an SQL expression, do not use the schema parameter.
   schema	The owner of the table, which is typically the Oracle username in uppercase. Use this parameter to access tables that do not belong to the current connection user.
   max_long	The maximum length of columns of the LONG, LONG RAW, and XMLTYPE data types in the Oracle table. Value range: 1 to 1073741823. Default value: 32767.
   readonly	Sets the Oracle table to read-only, which prevents INSERT, UPDATE, and DELETE operations.
   sample_percent	The sampling percentage of the Oracle table data that is used for PostgreSQL table statistics. Value range: 0.000001 to 100. Default value: 100.
   prefetch	The number of rows transferred at a time between PostgreSQL and Oracle during a foreign table scan. Value range: 0 to 1024. Default value: 200. A value of 0 disables the prefetch feature.

After you complete these steps, you can operate on the Oracle table through the foreign table. Basic operations such as DELETE, INSERT, UPDATE, and SELECT are supported. You can also import foreign table definitions by running the following command:

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