advertisements
_____________________________________________________________________________________________________________________
Problem Description:
Select from a synonym is failed with ORA-00980: synonym translation is no longer valid. There are few reasons for this error.
- 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.
- Check whether you created the synonym for a non existing object.
- 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:
ORA-04043: object dept does not exist
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment