Skip to Main Content
  • Questions
  • ORA-00980: synonym translation is no longer valid

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question.

Asked: December 31, 2002 - 6:23 pm UTC

Last updated: December 17, 2014 - 6:31 pm UTC

Version: 9i

Viewed 100K+ times! This question is

You Asked

Hi Tom,

I run Oracle 7.3 for years in Window NT machine with an C++ application and it work fine.

I recently updated my Oracle to 9i in Windows 2000 to my new machine (different machine). After Imported the database from my old machine to a new one my application wrote in C++ no longer work and came up with an error

*********
ERROR:
CDBException Generated.
ORA-00980: synonym translation is no longer valid
Option value changed.

Please close and Restart Application.

***************

Thanks. your answer is greatly appreciated.

David


and Tom said...

that means you lost some object/access to some object when you did your move. That error simply means "synonym is still here, but the object it points to is inaccessible". It could be inaccessible due to a missing grant, or due to the object not being there.

You need to find out what synonym it is, query the data dictionary to figure out what object it points to and figure out why you no longer have access to that object.

ops$tkyte@ORA920> create synonym s for t;
Synonym created.

ops$tkyte@ORA920> select * from s where rownum = 1;

C
----------
1

ops$tkyte@ORA920> drop table t;
Table dropped.

ops$tkyte@ORA920> select * from s where rownum = 1;
select * from s where rownum = 1
*
ERROR at line 1:
ORA-00980: synonym translation is no longer valid



Rating

  (17 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

ORA-00980: synonym translation is no longer valid", version 9i

A reader, January 01, 2003 - 2:07 am UTC

Thanks Tom your answer is great help.

David

Even I am facing the same problem

Jignesh, March 01, 2005 - 12:51 pm UTC

-- --------------------------------
-- CONNECT TO BRIDGE DATABASE
-- -------------------------------- 

SQL> select banner from v$version;

BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.0.0 - 64bit Production
PL/SQL Release 8.1.7.0.0 - Production
CORE    8.1.7.0.0       Production
TNS for Solaris: Version 8.1.7.0.0 - Production
NLSRTL Version 3.4.1.0.0 - Production

SQL> select * from USER_synonyms where synonym_name = 'BANKS';

SYNONYM_NAME                   TABLE_OWNER                    TABLE_NAME                     DB_LINK
------------------------------ ------------------------------ ------------------------------ -------
BANKS                                                         BANKS                          MISPPOU.TEST.BACS.CO.UK

-- Please note that the synonym is pointing to 7.3 database

SQL> select * from banks where rownum < 3;

      CODE NAME
---------- --------------------
         1 BANK OF ENGLAND
         2 BARCLAYS BANK PLC
         

We have created a Database link between Bridge Database and a Target Database
----------------------------------------------         
CONNECT TO TARGET DATABASE
----------------------------------------------         

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS for Solaris: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

SQL> select * from banks@DB_MIBRIDGE.TEST.BACS.CO.UK where rownum < 3;

      CODE NAME
---------- --------------------
         1 BANK OF ENGLAND
         2 BARCLAYS BANK PLC
         

But... In PLSQL :

declare
  Cursor c1 is select * from 
  banks@DB_MIBRIDGE.TEST.BACS.CO.UK where rownum < 3;
begin
  For i in c1 loop
     Null;
  end loop;
end;

  Cursor c1 is select * from
               *
ERROR at line 2:
ORA-06550: line 2, column 16:
PL/SQL: ORA-00980: synonym translation is no longer valid
ORA-06550: line 2, column 16:
PL/SQL: SQL Statement ignored

Thanks
 

Tom Kyte
March 01, 2005 - 12:56 pm UTC

turn on sql_trace=true and see what recursive sql statement is actually failing during the compilation of the block perhaps.

question : Jignesh

jpaugert, June 03, 2005 - 12:50 pm UTC

Hi Jignesh,

did you resolv your pb of the pl/sql error ORA-00980?

synonym translation over db link

yassin, January 26, 2007 - 4:27 pm UTC

Hi Tom,
I have a synonym which points to a remote table.
When I try to fire an insert statement with sql plus it works, but when I try to create a procedure with the same insert statement it fails.

cbcm_customer@ROAM> insert into cbcm_emr_wasl_transfer
2 values ('0', 2323, 0, 'rr', 'r', 'r', 23, '23', sysdate
3 , 'ee', '1', '1', '1', '1');

1 row created.

cbcm_customer@ROAM> rollback;

Rollback complete.

cbcm_customer@ROAM> create or replace procedure t_proc
2 as
3 begin
4 insert into cbcm_emr_wasl_transfer
5 values ('0', 2323, 0, 'rr', 'r', 'r', 23, '23', sysdate
6 , 'ee', '1', '1', '1', '1');
7 end;
8 /

Warning: Procedure created with compilation errors.

cbcm_customer@ROAM> show errors
Errors for PROCEDURE T_PROC:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/3 PL/SQL: SQL Statement ignored
4/15 PL/SQL: ORA-00980: synonym translation is no longer valid
cbcm_customer@ROAM>


Am I doing anything wrong.

ora-980 with db link through a view

Ray, March 08, 2007 - 3:02 am UTC

Hi Tom,
I am trying to create a view that combines a union all over a dblink. The local DB version is 10.2.0.1 and the remote version is 8.1.7
when I run the query as is, it works.
I created a view for the query, but when I query the view I get the ora-980.

this works:
select col1,col2,col3
from (select col1,col2,function@remote_db
from remote_table1@remote_db
union all
select col1,col2,col3
from local_table)

when I change it to this:
create view local_view (col1,col2,col3) as
select col1,col2,col3
from (select col1,col2,function@remote_db
from remote_table1@remote_db
union all
select col1,col2,col3
from local_table)

I get the ora-980
any ideas?

Thanks

ORA-00980: synonym translation is no longer valid

Nira, May 29, 2008 - 12:23 pm UTC

Hi Tom,

I had a same issue yesterday. There was a synonym in a database which is pointing to the another database object through a db link. While I am trying to select from the synonym it was giving me the above error. i.e. synonym translation is no longer valid. While I tried to execute the select statement using db link itself works fine. For this issue I tried to create and replace the synonym first. But it was not working. I tried to drop the synonym then the session go aborted with server terminated message. I reconnected and retried to drop the synonym. Then it got dropped and I recreated the synonym. After that the issue got resolved. But I am not sure why the synonym got invalid. The underlying remote object has not been changed also. I faced same issue twice. So If you have any clue please give me your comments.

My findings:

- The moment synonym got invalid source server alert log got following error
Wed May 28 12:20:25 2008

ORA-00600: internal error code, arguments: [OCIKCallPush: deprecated], [], [], [], [], [], [], []
ORA-00018: maximum number of sessions exceeded

- Alert log from the db1

Wed May 28 12:20:31 2008

Error 18 trapped in 2PC on transaction 46.9.64819. Cleaning up.
Error stack returned to user:
ORA-00018: maximum number of sessions exceeded
ORA-02063: preceding line from DB1_DB2_LINK

There could be any reason behind the maximum number of sessions exceeded with synonym translation error.

Tom Kyte
May 29, 2008 - 1:24 pm UTC

sure, it could well have been at that point in time the synonym could not be translated because the remote database was unreachable. You couldn't establish a connection at all.

ora-00980:synonym translation is no longer valid

Naidu, October 04, 2010 - 10:57 am UTC

Hi Tom,

I am working as a oracle apps technical consultant.Recently i am getting one error.i.e,ora-00980:synonym translation is no longer valid.

sql>select * from po_headers_interface;
ora-00980:synonym translation is no longer valid.

How is it possible for drop a interface table? Please tell me the solution?.
Tom Kyte
October 05, 2010 - 1:52 am UTC

ops$tkyte%ORA11GR2> !oerr ora 980
00980, 00000, "synonym translation is no longer valid"
// *Cause: A synonym did not translate to a legal target object. This 
//         could happen for one of the following reasons:
//         1. The target schema does not exist.
//         2. The target object does not exist.
//         3. The synonym specifies an incorrect database link.
//         4. The synonym is not versioned but specifies a versioned
//            target object.
// *Action: Change the synonym definition so that the synonym points at
//          a legal target object.


work with your DBA - someone dropped something - nothing I can do sitting here - something your DBA is going to have to remedy.

Anyone with the privileges can drop stuff - that is what happened here. Someone changed something.

ORA-00980: synonym translation is no longer valid

Mukesh, August 12, 2013 - 7:54 pm UTC

Tom, as always your help is highly appreciated.

I'm trying to use another schema's table via a database link to the same database (For some testing I'm creating the other schema in the same database; otherwise it was on a remote database). I can select from table using synonym but any procedure using it can't be compiled successfully. What could be the reason and solution to this problem?

Oracle 11.2.0.3.0 - 64bit Production


create user A identified by AA
/

create user B identified by BB
/

grant create session, resource, create table, create database link, create synonym to A, B
/

create table A.tab_a (name varchar2(2))
/

connect B/BB

create database link dblink_to_samedb@loopback connect to A identified by AA using <service_to_same_db>
/

create or replace synonym tab_a for tab_a@dblink_to_samedb@loopback
/


create or replace procedure test
as
a number(2);
begin
select count(*) into a from tab_a;
end;
/
show errors
/

Warning: compiled but with compilation errors
Errors for PROCEDURE TEST

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/29 PL/SQL: ORA-00980: synonym translation is no longer valid
5/1 PL/SQL: SQL Statement ignored

select count(*) from tab_a
/

COUNT(*)
----------
0
1 row selected.

select count(*) from tab_a@GWPIM02.GWP.SEIC.COM@loopback
/

COUNT(*)
----------
0
1 row selected.


drop user A cascade
/

drop user b cascade
/

Tom Kyte
August 12, 2013 - 8:27 pm UTC

I cannot reproduce, do you have anything else in the database (schema, object, anything) that has that same name?

I ran your script verbatim - changing only the create database link... 11.2.0.3 64bit production..

can you run the script from start to finish in sqlplus like this and post the exact cut and paste?

ops$tkyte%ORA11GR2> drop user A cascade;

User dropped.

ops$tkyte%ORA11GR2> drop user b cascade;

User dropped.

ops$tkyte%ORA11GR2> create user A identified by AA;

User created.

ops$tkyte%ORA11GR2> create user B identified by BB;

User created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> grant create session, resource, create table, create database link, create synonym to A, B;

Grant succeeded.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create table A.tab_a (name varchar2(2));

Table created.

ops$tkyte%ORA11GR2> connect B/BB
Connected.
b%ORA11GR2>
b%ORA11GR2> create database link loopback@ora11gr2
  2  connect to A identified by AA
  3  using 'ora11gr2';

Database link created.

b%ORA11GR2>
b%ORA11GR2> create or replace synonym tab_a for tab_a@loopback@ora11gr2;

Synonym created.

b%ORA11GR2>
b%ORA11GR2> create or replace procedure test
  2  as
  3  a number(2);
  4  begin
  5  select count(*) into a from tab_a;
  6  end;
  7  /

Procedure created.

b%ORA11GR2> show errors
No errors.
b%ORA11GR2> /

Procedure created.

b%ORA11GR2>
b%ORA11GR2> select count(*) from tab_a
  2  /

  COUNT(*)
----------
         0

b%ORA11GR2>
b%ORA11GR2> select count(*) from tab_a@loopback@ora11gr2;

  COUNT(*)
----------
         0

b%ORA11GR2>
b%ORA11GR2> exec test

PL/SQL procedure successfully completed.

ORA-00980: synonym translation is no longer valid

Shimmy, August 13, 2013 - 3:50 pm UTC

Don't we have to issue GRANT SELECT explicitly to user B to use A.tab_a in a procedure?
GRANT SELECT ON A.tab_a TO B;
Tom Kyte
August 13, 2013 - 7:25 pm UTC

no, look at the database link, it connects as A. It can access whatever A can access.

Re-executed

Mukesh, August 14, 2013 - 2:40 pm UTC

Tom, I tried to execute the same script but I had to change the database link name.

drop user A cascade;
drop user b cascade;
create user A identified by AA;
create user B identified by BB;
grant create session, resource, create table, create database link, create synonym to A, B;
create table A.tab_a (name varchar2(2));

connect B/BB

create database link service_name@ora11gr2
connect to A identified by AA
using 'service_name';

create or replace synonym tab_a for tab_a@service_name@ora11gr2;

create or replace procedure test
  as
  a number(2);
  begin
  select count(*) into a from tab_a;
  end;
  /
  
show errors;

select count(*) from tab_a;
select count(*) from tab_a@service_name@ora11gr2;

exec test;


User dropped.
User dropped.
User created.
User created.
Grant complete.
Table created.
Connected as B@service_name
Database link created.
Synonym created.
Warning: compiled but with compilation errors
Errors for PROCEDURE TEST

LINE/COL ERROR                                                            
-------- -----------------------------------------------------------------
5/31     PL/SQL: ORA-00980: synonym translation is no longer valid        
5/3      PL/SQL: SQL Statement ignored                                    

  COUNT(*)
----------
         0
1 row selected.

  COUNT(*)
----------
         0
1 row selected.
BEGIN test; END;
Error at line 30
ORA-06550: line 1, column 7:
PLS-00905: object B.TEST is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


Tom Kyte
August 14, 2013 - 3:27 pm UTC

when you say 'service_name' what do you mean. isn't that a tnsnames.ora entry - something in the form

tnsentry = (description......)


so it isn't a service name really, it is a tns entry right?


give me a soup to nuts example - are you really using the words "service_name" and how are you configured network wise?


b%ORA11GR2> connect /
Connected.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> drop user A cascade;

User dropped.

ops$tkyte%ORA11GR2> drop user b cascade;

User dropped.

ops$tkyte%ORA11GR2> create user A identified by AA;

User created.

ops$tkyte%ORA11GR2> create user B identified by BB;

User created.

ops$tkyte%ORA11GR2> grant create session, resource, create table, create database link, create
  2  synonym to A, B;

Grant succeeded.

ops$tkyte%ORA11GR2> create table A.tab_a (name varchar2(2));

Table created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> connect B/BB
Connected.
b%ORA11GR2>
b%ORA11GR2> create database link ora11gr2_tns_entry@ora11gr2
  2  connect to A identified by AA
  3  using 'ora11gr2_tns_entry';

Database link created.

b%ORA11GR2>
b%ORA11GR2> create or replace synonym tab_a for tab_a@ora11gr2_tns_entry@ora11gr2;

Synonym created.

b%ORA11GR2>
b%ORA11GR2> create or replace procedure test
  2    as
  3    a number(2);
  4    begin
  5    select count(*) into a from tab_a;
  6    end;
  7    /

Procedure created.

b%ORA11GR2>
b%ORA11GR2> show errors;
No errors.
b%ORA11GR2>
b%ORA11GR2> select count(*) from tab_a;

  COUNT(*)
----------
         0

b%ORA11GR2> select count(*) from tab_a@ora11gr2_tns_entry@ora11gr2;

  COUNT(*)
----------
         0

b%ORA11GR2>
b%ORA11GR2> exec test;

PL/SQL procedure successfully completed.

b%ORA11GR2>
b%ORA11GR2> !cat $ORACLE_HOME/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /home/ora11gr2/app/ora11gr2/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORA11GR2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dellpe)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ora11gr2)
    )
  )

ORA11GR2_TNS_ENTRY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dellpe)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ora11gr2)
    )
  )


Re-executed

Mukesh, August 14, 2013 - 3:29 pm UTC

I executed the previous script in some other editor. Executed again in sqlplus. But result is same only presentation is different.

You asked "do you have anything else in the database (schema, object, anything) that has that same name? "
I don't think so. If it was the case I might have got error "ORA-00955: name is already being used by existing object". Isn't it?

SQL> drop user A cascade;

User dropped.

SQL> drop user b cascade;

User dropped.

SQL> create user A identified by AA;

User created.

SQL> create user B identified by BB;

User created.

SQL> grant create session, resource, create table, create database link, create synonym

Grant succeeded.

SQL> create table A.tab_a (name varchar2(2));

Table created.

SQL>
SQL> connect B/BB@service_name
Connected.
SQL>
SQL> create database link service_name@ora11gr2
  2  connect to A identified by AA
  3  using 'service_name';

Database link created.

SQL>
SQL> create or replace synonym tab_a for tab_a@service_name@ora11gr2;

Synonym created.

SQL>
SQL> create or replace procedure test
  2    as
  3    a number(2);
  4    begin
  5    select count(*) into a from tab_a;
  6    end;
  7    /

Warning: Procedure created with compilation errors.

SQL>
SQL> show errors;
Errors for PROCEDURE TEST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/3      PL/SQL: SQL Statement ignored
5/31     PL/SQL: ORA-00980: synonym translation is no longer valid
SQL>
SQL> select count(*) from tab_a;

  COUNT(*)
----------
         0

SQL> select count(*) from tab_a@service_name@ora11gr2;

  COUNT(*)
----------
         0

SQL>
SQL> exec test;
BEGIN test; END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00905: object B.TEST is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Tom Kyte
August 14, 2013 - 3:51 pm UTC

query dba_objects and dba_users to see if there is another object/schema out there that overlaps the namespace please.

Re-executed

Mukesh, August 14, 2013 - 3:45 pm UTC

I see the confusion here. I tried to hide company information by replacing the actual tns name/database name with service_name.

connect B/BB@service_name --service_name is a tns entry

SQL> create database link service_name@ora11gr2 --service_name is database's glabal name.
ora11gr2 is just a connection qualifier because I was getting an error ORA-02082: a loopback database link must have a connection qualifier while creating database link to the same database.

2 connect to A identified by AA
3 using 'service_name'; -- service_name is tns name

I'm not sure if I'm very clear.

This issue seem to appear with all the database objects' synonyms not just tables e.g. sequences, procedures.
Tom Kyte
August 14, 2013 - 3:54 pm UTC

give soup to nuts using the actual text you used - from start to finish. don't hide anything please.

also show the output of:


select name || ' = ' || value from v$parameter where isdefault = 'FALSE';


Here is soup-to-nuts

Mukesh, September 04, 2013 - 1:23 pm UTC


SQL> drop user A cascade;

User dropped.

SQL> drop user b cascade;

User dropped.

SQL> create user A identified by AA;

User created.

SQL> create user B identified by BB;

User created.

SQL> grant create session, resource, create table, create database link, create synonym to A, B;

Grant succeeded.

SQL> create table A.tab_a (name varchar2(2));

Table created.

SQL> connect B/BB@POPKIT2.POP.AMCO.COM
Connected.

SQL> CREATE DATABASE LINK "POPKIT2.POP.AMCO.COM@LB"
  2  CONNECT TO A
  3  IDENTIFIED BY AA
  4  USING 'KIT2_CRT.AMCO.COM';

Database link created.

SQL> create or replace synonym tab_a for tab_a@POPKIT2.POP.AMCO.COM@LB;

Synonym created.


SQL> create or replace procedure test
  2    as
  3    a number(2);
  4    begin
  5    select count(*) into a from tab_a;
  6    end;
  7    /

Warning: Procedure created with compilation errors.

SQL>
SQL> show errors;
Errors for PROCEDURE TEST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/3      PL/SQL: SQL Statement ignored
5/31     PL/SQL: ORA-00980: synonym translation is no longer valid
SQL>
SQL> select count(*) from tab_a;

  COUNT(*)
----------
         0

SQL> select count(*) from tab_a@POPKIT2.POP.AMCO.COM@LB;

  COUNT(*)
----------
         0

SQL>
SQL> exec test;
BEGIN test; END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00905: object B.TEST is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

KIT2_CRT.AMCO.COM =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = POPKIT2.POP.AMCO.COM)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = KIT2_CRT.AMCO.COM)
    )
  )
  

I queried dba_objects and dba_users to see if there is another object/schema out there that overlaps the namespace and I don't see any.

Here is the output for parameters -
processes = 3500
sessions = 5312
sga_max_size = 53687091200
use_large_pages = ONLY
shared_pool_size = 6442450944
java_pool_size = 67108864
_shared_pool_reserved_pct = 10
spfile = +DG_KIT2_DATA/POPKIT2/spfilePOPKIT2.ora
nls_length_semantics = CHAR
_ksmg_granule_size = 33554432
sga_target = 53687091200
control_files = +DG_KIT2_DATA/POPKIT2/control01.ctl, +DG_KIT2_DATA/POPKIT2/control02.ctl
db_block_size = 8192
compatible = 11.2.0.0.0
log_archive_dest_1 = LOCATION=+DG_DB10_FRA mandatory reopen=300  MAX_FAILURE=50 valid_for=(online_logfile,all_roles)
db_files = 2000
cluster_database = TRUE
thread = 1
undo_tablespace = UNDOTBS1
instance_number = 1
recyclebin = OFF
sec_case_sensitive_logon = FALSE
remote_login_passwordfile = EXCLUSIVE
db_domain = AMCO.COM
global_names = TRUE
dispatchers = (PROTOCOL=TCP) (SERVICE=POPKIT2XDB)
local_listener = POPKIT21_LOCAL
remote_listener = POPKIT2_REMOTE
remote_dependencies_mode = SIGNATURE
job_queue_processes = 50
_job_queue_interval = 5
_fix_control = 5240264:OFF, 7679164:OFF
result_cache_max_size = 0
audit_file_dest = /u09/app/oracle/admin/POPKIT2/adump
open_links = 50
open_links_per_instance = 50
audit_trail = NONE
db_name = POPKIT2
open_cursors = 3000
os_authent_prefix = 
pga_aggregate_target = 5368709120
_optimizer_join_elimination_enabled = FALSE
_optimizer_enable_extended_stats = FALSE
_optimizer_use_feedback = FALSE
diagnostic_dest = /u09/app/oracle
_trace_files_public = TRUE

V$VERSION output -
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production


Tom Kyte
September 09, 2013 - 9:18 am UTC

I fail at

connect B/BB@POPKIT2.POP.AMCO.COM


what is POPKIT2.POP.AMCO.COM


and do you know for sure that on that database, the tnsnames.ora for THAT databaseis identical to the tnsnames.ora you are running on yourself (each database could have a different tnsnames.ora)


and what database are you "starting" in. This isn't quite soup to nuts. It is still just the middle

Additional information regarding ORA-00980

Jay Davis, May 20, 2014 - 8:15 pm UTC

I ran into this problem today, in Oracle 11g 11.2.0.3.0 and found it was caused by lack of granted permissions in the target DB. Synopsis: DW has dblink to OLTP, and calls a function@oltp, receives ORA-00980: synonym translation no longer valid. After much research, including reading and trying examples on this page, after discussion with another DBA, I went to OLTP and granted execute on the function called by DW. Problem is solved. It had nothing to do with synonyms. I hope this is helpful.

hafyen rim, December 13, 2014 - 10:07 am UTC

GRANT UPDATE
ON SP
TO PUBLIC
Rapport d'erreur -
Erreur SQL : ORA-00980: La traduction de synonymes n'est plus valide
00980. 00000 - "synonym translation is no longer valid"
*Cause: A synonym did not translate to a legal target object. This
could happen for one of the following reasons:
1. The target schema does not exist.
2. The target object does not exist.
3. The synonym specifies an incorrect database link.
4. The synonym is not versioned but specifies a versioned
target object.
*Action: Change the synonym definition so that the synonym points at
a legal target object.

Tom Kyte
December 17, 2014 - 6:31 pm UTC

so, your synonym is invalid. what do you want me to comment on?

A reader, December 29, 2014 - 6:13 pm UTC

The object either doesn't exist or you don't have grants on the object

Oracle limitation for PLSQL relating to ORA-00980

Huy, August 04, 2015 - 10:16 am UTC

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

2. 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.
 

More to Explore

Multitenant

Need more information on Multitenant? Check out the Multitenant docs for the Oracle Database