Ошибка ora 00980 synonym translation is no longer valid

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';

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;

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;

In this case, the table has been renamed to COUNTRIES_BAK. In most cases, the base object don’t exist, which caused the synonym no longer valid.

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 valid 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;

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 it then create a new synonym.

SQL> drop synonym countries;

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

4. Drop the Invalid 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 anytime.

Thank you to everyone who tried to help. This turned out to be an Oracle limitation:

https://support.oracle.com/rs?type=doc&id=453754.1

APPLIES TO:

PL/SQL — Version 9.2.0.8 and later Information in this document
applies to any platform.
Checked for relevance on 01-Apr-2015

SYMPTOMS

A PL/SQL block fails with error: ORA-00980: synonym translation is no
longer valid, when selecting data from a remote database. The
following code demonstrates this issue:

On DB3 (create the table)

CONNECT u3/u3 DROP TABLE tab; CREATE TABLE tab(c1 number); INSERT
INTO tab VALUES (1); COMMIT;

On DB2 (create a synonym to the table on DB3)

CONNECT u2/u2 DROP DATABASE LINK dblink2; CREATE DATABASE LINK
dblink2 CONNECT TO u3 IDENTIFIED BY u3 USING ‘EMT102U6’; SELECT *
FROM global_name@dblink2; DROP SYNONYM syn2; CREATE SYNONYM syn2
FOR tab@dblink2; SELECT * FROM syn2;

On DB1 (create a synonym to the synonym on DB2)

CONNECT u1/u1 DROP DATABASE LINK dblink1; CREATE DATABASE LINK
dblink1 CONNECT TO u2 IDENTIFIED BY u2 USING ‘EMT102W6’; SELECT *
FROM global_name@dblink1; DROP SYNONYM syn1; CREATE SYNONYM syn1
FOR syn2@dblink1; SELECT c1 from syn1;

This works in SQL but fails when called from PL/SQL

DECLARE num NUMBER; BEGIN SELECT c1 INTO num FROM syn1; END;
/

ERROR at line 4: ORA-06550: line 4, column 3: PL/SQL: ORA-00980:
synonym translation is no longer valid ORA-06550: line 4, column 3:
PL/SQL: SQL Statement ignored

CAUSE

This issue was reported in Bug 2829591 QUERING FROM A PL/SQL
PROCEDURE IN 9I -> 8I-> 7.3.4, GETTING ORA-980. This bug was closed
as ‘NOT A BUG’ for the following reasons

PL/SQL cannot instruct middle database (DB2) to follow the database
link during the compilation phase. Therefore in order for this PL/SQL
block to compile and run, both database links dblink1 and dblink2
should be defined on the front end database — DB1. During runtime
database link dblink2 will be looked up in DB2 as expected.

SOLUTION

To implement the solution, please execute the following steps:

1. Create a database link dblink2 on DB1 pointing to DB3

SQL> create database link dblink2 connect to u3 identified by u3 using
‘EMT102U6’;

1. Create and compile the PL/SQL block on DB1.

CREATE DATABASE LINK dblink2 CONNECT TO u3 IDENTIFIED BY u3 USING
‘EMT102U6’;

SELECT * FROM global_name@dblink2; DECLARE num NUMBER; BEGIN
SELECT c1 INTO num FROM syn1; END; / PL/SQL procedure successfully
completed.

TIP: Another option is to use dyanmic SQL in the PL/SQL block as a
work around. When using dynamic SQL the database link is not resolved
at compile time but at runtime.

[{«Product»:{«code»:»SSTQPQ»,»label»:»IBM Cognos Series 7 PowerPlay»},»Business Unit»:{«code»:»BU053″,»label»:»Cloud & Data Platform»},»Component»:»Impromptu»,»Platform»:[{«code»:»PF025″,»label»:»Platform Independent»}],»Version»:»Impromptu 4.01″,»Edition»:»»,»Line of Business»:{«code»:»LOB10″,»label»:»Data and AI»}}]