How to Resolve ORA-00980: synonym translation is no longer valid

  • by

ORA-00980

We usually use synonyms to point to a base object that we don't own it. Let's see a case.

SQL> conn oe/oe@orclpdb1
Connected.
SQL> select * from countries;
select * from countries
              *
ERROR at line 1:
ORA-00980: synonym translation is no longer valid

In this case, when we tried to access the base object, we got an error. ORA-00980 means that the synonym that you're trying to use is invalid, furthermore, the base object is not existing.

Let's see what base object that the synonym points to.

SQL> column table_owner format a15;
SQL> column table_name format a15;
SQL> select table_owner, table_name from all_synonyms where owner = 'OE' and synonym_name = 'COUNTRIES';

TABLE_OWNER     TABLE_NAME
--------------- ---------------
HR              COUNTRIES

If the base object is a table, we can check whether the table is existing or not.

SQL> select t.owner, t.table_name from all_synonyms s, all_tables t where s.owner = 'OE' and s.synonym_name = 'COUNTRIES' and s.table_owner = t.owner and s.table_name = t.table_name;

no rows selected

Apparently, it's no longer existing. Next, we can check all tables of HR that we can access.

SQL> select table_name from all_tables where owner = 'HR' order by 1;

TABLE_NAME
---------------
COUNTRIES_BAK
DEPARTMENTS
EMPLOYEES
JOBS
JOB_HISTORY
LOCATIONS

6 rows selected.

In this case, the table has been renamed to COUNTRIES_BAK. In most cases, the base object don't exist.

Solutions

Here I provide 4 ways to solve ORA-00980.

1. Restore the Base Object

If you need the base object very much, you should ask for the owner or DBA to restore it. After that, the synonym will go alive again.

2. Replace Synonym Translation

You can create or replace the synonym with the same name to point to the new table.

SQL> create or replace synonym countries for hr.countries_bak;

Synonym created.

The good thing is that, you don't have to change our PL/SQL or application codes because you just replaced the translation underneath.

3. Recreate a New Synonym

To align with the new name of the base object, you can drop then create a new synonym.

SQL> drop synonym countries;

Synonym dropped.

SQL> create synonym countries_bak for hr.countries_bak;

Synonym created.

That is to say, we use the new synonym instead of the old synonym from now on.

4. Drop the Synonym

If the base object is no way to restore and the above solutions is not applicable to your case, then the synonym is useless, you can drop it.

Leave a Reply

Your email address will not be published. Required fields are marked *