DBA_SYNONYMS - Query Oracle Public & Private Synonyms

DBA_SYNONYMS

Overview

DBA_SYNONYMS describes every public and private synonym in the database. A synonym is an alias for another database object — a table, view, sequence, procedure, function, package, materialized view, or even another database link. Synonyms are fundamental to Oracle schema design because they allow application code to reference objects without embedding owner qualifiers, and they simplify cross-schema access, database link abstraction, and application portability.

DBAs query DBA_SYNONYMS during schema migrations to trace synonym chains, when investigating “table or view does not exist” errors caused by broken synonyms, and when auditing which public synonyms exist that could shadow private objects. The DB_LINK column reveals synonyms that resolve across database links, making this view also useful for distributed database auditing.

View Type: Static Data Dictionary View Available Since: Oracle 7 Required Privileges: SELECT on DBA_SYNONYMS, SELECT_CATALOG_ROLE, or SELECT ANY DICTIONARY

Key Columns

Column	Datatype	Description
OWNER	VARCHAR2(128)	Owner of the synonym; PUBLIC for public synonyms
SYNONYM_NAME	VARCHAR2(128)	Name of the synonym
TABLE_OWNER	VARCHAR2(128)	Owner of the object the synonym points to (despite the column name, this is not restricted to tables)
TABLE_NAME	VARCHAR2(128)	Name of the object the synonym resolves to
DB_LINK	VARCHAR2(128)	Database link name if the synonym points to a remote object; NULL for local objects

Essential Queries

Basic Usage

List all synonyms owned by a specific user, showing what each synonym resolves to:

SELECT
    owner,
    synonym_name,
    table_owner     AS resolves_to_owner,
    table_name      AS resolves_to_object,
    db_link         AS via_db_link
FROM
    dba_synonyms
WHERE
    owner = 'HR'
ORDER BY
    synonym_name;

Monitoring Query

Find all public synonyms that point to objects which no longer exist in the database (broken synonyms), which cause ORA-00942 errors at runtime:

SELECT
    s.synonym_name,
    s.table_owner,
    s.table_name,
    s.db_link
FROM
    dba_synonyms s
WHERE
    s.owner = 'PUBLIC'
    AND s.db_link IS NULL
    AND NOT EXISTS (
        SELECT 1
        FROM   dba_objects o
        WHERE  o.owner       = s.table_owner
        AND    o.object_name = s.table_name
    )
ORDER BY
    s.synonym_name;

Combined with Other Views

Resolve a full synonym chain — find where a public synonym ultimately points, including through database links:

SELECT
    s.owner,
    s.synonym_name,
    s.table_owner,
    s.table_name,
    s.db_link,
    o.object_type,
    o.status        AS object_status
FROM
    dba_synonyms s
    LEFT JOIN dba_objects o ON o.owner       = s.table_owner
                            AND o.object_name = s.table_name
                            AND o.object_type NOT IN ('INDEX', 'INDEX PARTITION')
WHERE
    s.owner = 'PUBLIC'
    AND s.db_link IS NULL
ORDER BY
    s.synonym_name;

Synonym Chain Detection

Find cases where a synonym points to another synonym (synonym chaining), which adds resolution overhead and can cause unexpected behaviour:

SELECT
    s.owner         AS synonym_owner,
    s.synonym_name,
    s.table_owner   AS points_to_owner,
    s.table_name    AS points_to_name,
    s2.table_owner  AS ultimate_owner,
    s2.table_name   AS ultimate_object
FROM
    dba_synonyms s
    JOIN dba_synonyms s2 ON s2.owner       = s.table_owner
                         AND s2.synonym_name = s.table_name
WHERE
    s.owner NOT IN ('SYS', 'SYSTEM')
ORDER BY
    s.owner,
    s.synonym_name;

Remote Object Synonyms

List all synonyms that resolve over database links, useful for distributed database auditing and identifying remote dependencies:

SELECT
    owner,
    synonym_name,
    table_owner,
    table_name,
    db_link
FROM
    dba_synonyms
WHERE
    db_link IS NOT NULL
ORDER BY
    db_link,
    owner,
    synonym_name;

Common Use Cases

ORA-00942 diagnosis — When a query fails with “table or view does not exist”, check DBA_SYNONYMS to confirm whether the synonym exists, that it points to the correct owner and object name, and that the target object is valid
Schema migration planning — Before moving objects to a new schema, query DBA_SYNONYMS to find all synonyms that reference the old schema so they can be updated or recreated
Public synonym shadow audit — Identify public synonyms whose name matches a private object in a user’s schema, which can cause name resolution ambiguity and unexpected behaviour
Database link dependency mapping — Filter DB_LINK IS NOT NULL to build a map of which remote databases each schema depends on through synonyms
Synonym cleanup — After decommissioning an application schema, find all remaining public synonyms that pointed to the old schema’s objects and drop them to avoid pollution of the PUBLIC namespace
Cross-schema access pattern analysis — Join with DBA_OBJECTS to understand which schemas are most heavily accessed via synonyms from other schemas

DBA_VIEWS — Views are common targets for synonyms; use DBA_SYNONYMS to find synonym aliases for views
DBA_OBJECTS — Confirms whether the synonym’s target object exists and whether it is VALID or INVALID
DBA_DB_LINKS — Describes the database links referenced in DBA_SYNONYMS.DB_LINK
DBA_DEPENDENCIES — Shows objects that depend on a synonym; useful when determining the full impact of dropping a synonym
ALL_SYNONYMS — Restricts the view to synonyms accessible to the current user; safer than DBA_SYNONYMS for application-level queries

Version Notes

Oracle 7 / 8: Public synonyms established as the standard mechanism for schema-independent object access; the five-column structure has remained stable ever since
Oracle 10g: No structural changes; automatic statistics gathering began tracking synonym resolution overhead in V$SQL_PLAN
Oracle 11g: Edition-Based Redefinition introduced; editioning synonyms behave like regular synonyms in DBA_SYNONYMS but have edition-specific resolution rules at runtime
Oracle 12c (Multitenant): In a CDB, querying DBA_SYNONYMS from the root returns synonyms for all containers; CON_ID column present to identify the container
Oracle 23ai: No structural changes to DBA_SYNONYMS; SQL Domains introduced as an alternative type of named database object, but domains do not use the synonym mechanism