DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-00980 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-00980: Synonym Translation Is No Longer Valid — Causes & Fixes

ORA-00980 occurs when an Oracle synonym exists in the data dictionary but the underlying object it points to — a table, view, sequence, or procedure — has been dropped, renamed, or become inaccessible. Oracle does not validate synonym targets at creation time, so this error only surfaces at query execution, often causing unexpected application outages in production environments.

Top 3 Causes

1. The Underlying Object Was Dropped

The most common cause: someone dropped the base table or view but left the synonym intact.

-- Check if the synonym's target object still exists
SELECT s.owner,
       s.synonym_name,
       s.table_owner,
       s.table_name
FROM   dba_synonyms s
LEFT JOIN dba_objects o
       ON  o.owner       = s.table_owner
      AND  o.object_name = s.table_name
WHERE  o.object_name IS NULL
  AND  s.db_link IS NULL;
Enter fullscreen mode Exit fullscreen mode

If this query returns rows, the synonym is pointing at a ghost — the object no longer exists.

2. Privileges Were Revoked on the Target Object

When a synonym points to an object owned by another schema, revoking the necessary grants makes the synonym invalid from the grantee's perspective.

-- Check current privileges on the target object
SELECT grantee,
       owner,
       table_name,
       privilege,
       grantable
FROM   dba_tab_privs
WHERE  owner      = 'HR'
  AND  table_name = 'EMPLOYEES';

-- Re-grant the required privilege
GRANT SELECT ON hr.employees TO app_user;
Enter fullscreen mode Exit fullscreen mode

3. DB Link Referenced by the Synonym Is Broken or Deleted

A synonym built on a database link becomes invalid if the DB Link is dropped or the remote object is renamed or removed.

-- Identify synonyms that rely on a DB Link
SELECT synonym_name,
       table_name,
       db_link
FROM   dba_synonyms
WHERE  db_link IS NOT NULL;

-- Test the DB Link connectivity
SELECT * FROM dual@your_db_link_name;

-- Recreate the DB Link if needed
DROP DATABASE LINK your_db_link_name;

CREATE DATABASE LINK your_db_link_name
CONNECT TO remote_user
IDENTIFIED BY "secret_password"
USING 'remote_tns_alias';
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Recreate or replace the synonym pointing it to the correct, existing object:

-- Drop the broken synonym
DROP SYNONYM app_user.broken_syn;

-- Or drop a public synonym (requires DBA privilege)
DROP PUBLIC SYNONYM broken_public_syn;

-- Recreate with the correct target
CREATE OR REPLACE SYNONYM app_user.emp_syn
FOR hr.employees;

-- Recreate a public synonym
CREATE OR REPLACE PUBLIC SYNONYM emp_syn
FOR hr.employees;
Enter fullscreen mode Exit fullscreen mode

Generate a cleanup script for all broken synonyms in bulk:

-- Auto-generate DROP statements for all invalid synonyms
SELECT 'DROP ' ||
       CASE WHEN owner = 'PUBLIC' THEN 'PUBLIC ' ELSE '' END ||
       'SYNONYM ' ||
       CASE WHEN owner != 'PUBLIC' THEN owner || '.' ELSE '' END ||
       synonym_name || ';' AS drop_statement
FROM   dba_synonyms s
WHERE  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 owner, synonym_name;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

1. Always check dependencies before dropping any object.
Make it a standard change-management step to query dba_dependencies before executing any DROP or RENAME statement in production.

-- Find all objects depending on a table before dropping it
SELECT name, type, referenced_owner, referenced_name
FROM   dba_dependencies
WHERE  referenced_owner = 'HR'
  AND  referenced_name  = 'EMPLOYEES';
Enter fullscreen mode Exit fullscreen mode

2. Schedule a regular invalid-synonym health check.
Run the query below as a daily Oracle Scheduler job and alert the DBA team if any rows are returned. Catching broken synonyms before the application hits them saves significant downtime.

-- Daily health check for invalid synonyms
SELECT s.owner,
       s.synonym_name,
       s.table_owner,
       s.table_name
FROM   dba_synonyms s
LEFT JOIN dba_objects o
       ON  o.owner       = s.table_owner
      AND  o.object_name = s.table_name
WHERE  o.object_name IS NULL
  AND  s.db_link IS NULL
  AND  s.owner NOT IN ('SYS','SYSTEM');
Enter fullscreen mode Exit fullscreen mode

Related Errors

  • ORA-00942 — table or view does not exist: similar symptom but triggered without a synonym layer.
  • ORA-01775 — looping chain of synonyms: synonyms circularly referencing each other.
  • ORA-02019 — connection description for remote database not found: DB Link itself cannot be resolved.

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)