Thanks Tom your answer is great help.

David

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

Hi Jignesh,

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

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.

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

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.

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

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.

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
/

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.

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, 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

tnsentry = (description......)

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)
    )
  )

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

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.

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

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.

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.

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

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.