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

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.

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.

Recover Dropped table Using Flashback

2. Check whether you created the synonym for a non existing object.
3. 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

Datapump Tuning Features

Datapump vs EXP/IMP

Datapump Advantages

Datapump Exclude Table Partition

Datapump Exclude and Include Parameter

Datapump NetworkLink Parameter

Datapump SQLFILE Parameter

Datapump Reuse_dumpfiles Parameter

Datapump Compression Parameter

Datapump Jobname Parameter

Datapump Remapdata Parameter

Datapump Sample Parameter

Datapump Query Parameter

Datapump Transportable Tablespace Option

_____________________________________________________________________________________________________________________

Website Stats