Using Oracle FDW to Access Oracle Database Remotely（oraclefdw）

The advent of foreign data wrappers(FDW) in Oracle database provides a convenient way to access Oracle Database remotely between databases. It allows users to create a foreign table on the local Oracle Database and access data on the remote side. In this article, I will introduce how to use Oracle FDW to access Oracle Database remotely.

Firstly, configure Oracle Database on both sides so that the database is accessible from the remote side. For example, Oracle Database on the remote side should have database link that can be accessed from the local database. In addition, an external table should also be created on the remote database, which will store the data to be accessed by the local database.

The next step is to install Oracle FDW on the local database. For Oracle 12c and higher version, Oracle FDW has been included in the database. So users don’t need to install Oracle FDW. For Oracle 11g, the Oracle FDW should be downloaded and installed on the local database.

After successful installation of Oracle FDW, it’s time to configure the foreign server and foreign table on the local database. A foreign server object is created on the local database to access the Oracle Database on the remote side. Foreign table is also created on the local database by referencing the remote external table.

Lastly, users can run SQL queries on the local database to access the data on the remote Oracle Database. The query result set is always generated from the remote Oracle Database but displayed on the local database.

In summary, Oracle FDW is a nice feature that allows users to access Oracle Database remotely with minimal configuration and effort. It supports multiple databases, making it a valuable tool for database administrators.