Select Failed with ORA-00980: synonym translation is no longer valid

Problem Description:
Select from a synonym is failed with ORA-00980: synonym translation is no longer valid. There are few reasons for this error.
  1. Check the underlying table is deleted or not. If you still need the table and synonym check the table is available in recycle bin and restore it back.
  1. Check whether you created the synonym for a non existing object.
  2. Check whether the user has been deleted where your synonym is pointing to.

See some relevant examples here.

Example 1. Deleting the synonym resolving reference object
SQL> create synonym SYN_employee for employee;

Synonym created.

SQL> select * from SYN_employee;

EMP_NAME                             DEPT        SAL
------------------------------ ---------- ----------
Rupal                                  10       5000
Hero                                   10       5500
Jain                                   10       4000
John                                   20       6000

SQL> drop table employee;

Table dropped.

SQL>  select * from SYN_employee;
 select * from SYN_employee
               *
ERROR at line 1:
ORA-00980: synonym translation is no longer valid

SQL> flashback table employee to before drop;

Flashback complete.

SQL> select * from SYN_employee;

EMP_NAME                             DEPT        SAL
------------------------------ ---------- ----------
Rupal                                  10       5000
Hero                                   10       5500
Jain                                   10       4000
John                                   20       6000

Example 2. Create a synonym for a non existing object.

SQL> create synonym syn_wrong for dept;

Synonym created.

SQL> select * from syn_wrong;
select * from syn_wrong
              *
ERROR at line 1:
ORA-00980: synonym translation is no longer valid


SQL> desc dept;
ERROR:
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google