Oracle Public Synonyms Don't Exist in Postgres — Here's the Migration Pattern

What Oracle Public Synonyms Actually Do

A public synonym in Oracle is a database-wide alias that maps an unqualified object name to a fully qualified one. When application code issues SELECT * FROM orders, Oracle resolves it by checking the current schema first, then checking the public synonym registry. If a public synonym orders points to appschema.orders_tbl, the query succeeds without the application knowing anything about schemas.

This design pattern is everywhere in Oracle shops. It exists for multiple reasons: legacy application code that predates schema-per-service patterns, third-party packages that hardcode unqualified names, and multi-tenant architectures where different users need to reference the same shared objects. A typical Oracle production database has hundreds to thousands of public synonyms. Tools like ora2pg catalog them and dutifully report them — then produce no migration output, because Postgres simply does not have the concept.

The most common symptom when this goes unaddressed during migration testing: the migrated application works fine in development (because the developer's search_path happens to match), then breaks in staging or production because the connection role has a different default search_path that doesn't find the objects.

How Postgres Name Resolution Works

Postgres resolves unqualified object names using the search_path configuration parameter. When you write SELECT * FROM orders, Postgres checks each schema listed in search_path left to right and returns the first match. The default search_path is "$user", public — which means Postgres first checks a schema named after the current role, then the public schema.

-- Check current effective search_path
SHOW search_path;

-- See what search_path is set at the database level
SELECT datname, datconfig
FROM pg_database
WHERE datname = current_database();

-- See role-level overrides
SELECT rolname, rolconfig
FROM pg_roles
WHERE rolname = current_user;

The resolution rules matter in a few ways that Oracle synonyms handle differently:

• Postgres resolves at query time, not definition time. If the search_path changes between sessions, the same unqualified name resolves to different objects. Oracle synonyms are static mappings — the synonym always points to the same target regardless of session context.
• search_path applies to all object types uniformly. Functions, sequences, types, and tables all use the same search_path. Oracle synonyms are per-object-type (you can have a synonym for a table and a different one for a package with the same name).
• There is no "public synonym" that works for all users by default. In Postgres, the public schema is world-readable (before Postgres 15), so placing objects in public with the right names effectively mimics the behavior — but it requires all connecting roles to have public in their search_path.

The Migration Pattern: search_path as the Oracle Synonym Layer

The standard migration approach maps Oracle's synonym resolution to Postgres's search_path in three steps.

Step 1: Inventory All Public Synonyms and Their Targets

-- Oracle: enumerate all public synonyms and their target schemas
SELECT
  s.synonym_name,
  s.table_owner,
  s.table_name,
  o.object_type
FROM dba_synonyms s
JOIN dba_objects o
  ON o.owner = s.table_owner
  AND o.object_name = s.table_name
WHERE s.owner = 'PUBLIC'
ORDER BY s.synonym_name;

The output tells you which synonym names you need to preserve and which schemas contain their targets. In most shops, 80–90% of public synonyms point to one or two application schemas. That simplifies the migration significantly.

Step 2: Design Your Postgres Schema Layout

The goal is to arrange object ownership so that the search_path resolves synonym names correctly. There are two approaches depending on how many schemas are involved.

Option A — Single application schema, set search_path at the database level. If all public synonyms point to objects in a single Oracle schema (e.g., APPSCHEMA), migrate that schema's objects to a Postgres schema of the same name and configure the database default:

-- Set database-level default search_path
ALTER DATABASE myapp SET search_path = appschema, public;

-- Verify
SELECT datconfig FROM pg_database WHERE datname = 'myapp';

Any connection to this database now resolves unqualified names against appschema first. Application code that issued SELECT * FROM orders against Oracle will issue the same query against Postgres and find appschema.orders without modification.

Option B — Multiple target schemas, use views in a single shim schema. When public synonyms point to objects scattered across multiple Oracle schemas, the single search_path approach breaks down — Postgres searches schemas left to right and stops at the first match, so if two schemas both contain a table called orders, only the first schema's version is visible.

The cleaner solution is a dedicated synonym schema:

-- Create a shim schema to hold synonym-equivalent views
CREATE SCHEMA syn;

-- For each public synonym, create a view in the shim schema
CREATE VIEW syn.orders AS SELECT * FROM appschema.orders_tbl;
CREATE VIEW syn.customers AS SELECT * FROM crm_schema.customers;
CREATE VIEW syn.products AS SELECT * FROM catalog_schema.products;

-- Set search_path to find the shim schema first
ALTER DATABASE myapp SET search_path = syn, public;

Views in the shim schema are updatable for simple tables (single-table, no aggregates, no DISTINCT), which matters if the application issues DML against synonym-named objects. For complex views, you'll need INSTEAD OF triggers or explicit redirects.

Step 3: Handle the Cases search_path Cannot Cover

search_path solves the common case but not all of them.

Private synonyms. Oracle also supports private synonyms — owned by individual schemas, not public. A private synonym in schema REPORTING that points to APPSCHEMA.orders_tbl requires a corresponding view in the Postgres reporting schema, not in the shim:

-- In Postgres, replicate Oracle private synonyms as schema-local views
CREATE VIEW reporting.orders AS SELECT * FROM appschema.orders_tbl;

Synonyms for functions and procedures. Postgres search_path resolves functions by name too, so if an Oracle public synonym points to a package function, migrate the function to the target schema and the search_path handles resolution. But if there are function name collisions across schemas, you need explicit schema qualification in the migrated code.

Synonyms for sequences. search_path resolves sequence names, so NEXTVAL('order_seq') resolves correctly if order_seq is in a search_path schema. The edge case is CURRVAL — Postgres CURRVAL is session-scoped, and if your application calls CURRVAL across connection pool boundaries (which poolers in transaction mode create), it will fail even with correct name resolution.

-- Verify sequence resolution
SELECT currval('order_seq');  -- Fails if nextval not yet called in this session

-- Better pattern for post-migration code
INSERT INTO orders (...) VALUES (...) RETURNING order_id;

Synonyms pointing to database links. Oracle allows public synonyms that point through database links to remote objects. Postgres foreign data wrappers replicate this, but the name resolution path is different — FDW foreign tables are first-class objects in a local schema, so search_path resolution applies normally once the FDW table is created.

Validation Before Cutover

After setting up the search_path configuration, validate resolution for every synonym that appears in application SQL. The most reliable approach is to extract the full application SQL corpus from Oracle's cursor cache and run it against Postgres in a test environment with the application user's credentials:

-- Oracle: extract application SQL from cursor cache for synonym analysis
SELECT DISTINCT s.sql_text
FROM v$sqlarea s
WHERE s.parsing_schema_name NOT IN (
  'SYS', 'SYSTEM', 'DBSNMP', 'WMSYS', 'OUTLN'
)
AND s.sql_text LIKE '%orders%'  -- one synonym at a time
ORDER BY 1;

On the Postgres side, verify each unqualified name resolves to the correct object:

-- Postgres: check where an unqualified name resolves
-- Connect as the application user and run:
SELECT n.nspname, c.relname, c.relkind
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname = 'orders'
AND n.nspname = ANY(
  string_to_array(
    current_setting('search_path'), ', '
  )
);

A mismatch here — where the name resolves to the wrong schema or doesn't resolve at all — will surface as a runtime error when the application connects. Better to find it during validation than during the cutover window.

The Role-Level search_path Override

One advantage of the Postgres approach is that different application users can have different search paths, enabling the same database to serve multiple applications with different synonym namespaces:

-- Application role: resolve through synonym shim schema
ALTER ROLE app_user SET search_path = syn, appschema, public;

-- Reporting role: resolve through reporting schema first
ALTER ROLE report_user SET search_path = reporting, appschema, public;

-- Admin role: require explicit schema qualification (no implicit resolution)
ALTER ROLE dba_user SET search_path = '';

This is actually more flexible than Oracle's public synonym model, where the synonym is globally visible to all users. In Postgres, you can give different user classes different resolution behavior without creating separate synonym objects for each.

What ora2pg Produces and What It Misses

ora2pg has an --export_schema SYNONYM mode that catalogs public synonyms, but its output is documentation rather than migration code. It generates a report showing synonym names and targets. What it does not generate is the Postgres DDL to implement them — no view definitions, no search_path configuration, no role-level overrides.

This means synonym migration falls into the manual engineering category. For a database with 500 public synonyms all pointing to one application schema, the fix is three lines of SQL (ALTER DATABASE, plus verification). For a database with 500 synonyms spread across 12 schemas with private synonyms for specific reporting users, it's several days of analysis and DDL generation.

Either way, it needs to be planned before the cutover checklist is written, not discovered during cutover rehearsal.