Start with the business access pattern

A nightly reporting read has very different requirements from a synchronous booking transaction that spans two systems. The right replacement therefore follows data flow, freshness, transaction boundaries and failure behaviour.

Map who accesses which remote object, why and how often. The existence of a link does not prove that it is still used; conversely, dynamically generated SQL may hide dependencies from static analysis.

Inventory Oracle links and their callers

Oracle · database links visible to the user
SELECT owner, db_link, username, host
FROM all_db_links
ORDER BY owner, db_link;

Visibility depends on privileges. Treat host names and technical usernames as internal architecture data. Enrich the inventory with SQL callers, remote objects being read or written, and the business requirement behind each access.

  • Read-only or write access?
  • Must data be available synchronously, or can it be delayed?
  • How do callers behave on timeout or connection loss?
  • Do multiple changes need to be treated as one business transaction?

Choose alternatives by access pattern

Federated access: postgres_fdw can access other PostgreSQL servers. It is not an Oracle driver. If Oracle remains on one side, a suitable Oracle FDW and its platform support must be evaluated separately.

Data copy or replication: Reporting may be better served by a local dataset. Define acceptable lag, initial load, change capture and reconciliation explicitly.

Service interface: Business writes can be decoupled through an application API. That changes error handling and transaction boundaries, so it is an architectural decision rather than a SQL substitution.

Example: decouple a reporting dependency

Assume a daily report needs the previous day’s inventory from another system. A controlled import into a local reporting table may satisfy the requirement. The import should carry a run identifier, data timestamp and reconciliation evidence.

The report should only be released when the import is complete. If a run fails, users must be able to see that the data is stale. Removing a remote dependency is not an improvement if it merely creates an invisible data gap.

Not a generic distributed-transaction replacement

A successful remote query does not prove equivalent behaviour for distributed writes. Commit, rollback and partial-failure semantics must be validated separately.

Accept the replacement by testing failure modes

  • Measure latency and data volume under realistic load.
  • Reconcile results with business checksums or comparison rules.
  • Test timeouts, revoked privileges and network interruptions.
  • For copied data, make maximum acceptable staleness visible.
  • For writes, test duplicates, retries and partial processing explicitly.

Group links by shared data flow in the migration plan. That often reveals system boundaries and migration waves before the implementation starts.

Primary sources & further reading

These recommendations are engineering guidance. Specific options depend on source and target versions, privileges and operating model.