While working with Oracle Applications schemas, you may encounter the error ORA-00980: synonym translation is no longer valid when accessing editioning views or objects through a synonym. This article explains a practical example of how this issue arises and how to resolve it.
Scenario
A user TEST tried to access the APPS schema object FND_USER_RESP_GROUPS_DIRECT using a synonym.
SQL> SHOW USER
USER is "TEST"
The user then connected and created a synonym for the editioning view:
SQL> CONN test/***
Connected.
SQL> CREATE OR REPLACE SYNONYM "FND_USER_RESP_GROUPS_DIRECT" FOR APPS.FND_USER_RESP_GROUPS_DIRECT;
Synonym created.
However, when querying the synonym, the following error occurred:
SQL> SELECT COUNT(1) FROM FND_USER_RESP_GROUPS_DIRECT;
SELECT COUNT(1) FROM FND_USER_RESP_GROUPS_DIRECT
*
ERROR at line 1:
ORA-00980: synonym translation is no longer valid
Root Cause
This issue happens because the TEST user is not edition-enabled, while the target object in the APPS schema is an editioning view.
Edition-based redefinition (EBR) in Oracle allows for multiple versions of application objects (like packages and views) across different editions.
When a non-editioned user tries to access editioned objects, Oracle cannot resolve the synonym, leading to this error.
To verify the editioning status of the user:
SQL> SELECT EDITIONS_ENABLED FROM DBA_USERS WHERE USERNAME='TEST';
E
-
N
The result N indicates the user is not edition-enabled.
Resolution
Enable editions for the user to allow access to editioned objects:
SQL> ALTER USER test ENABLE EDITIONS;
User altered.
Confirm the change:
SQL> SELECT EDITIONS_ENABLED FROM DBA_USERS WHERE USERNAME='TEST';
E
-
Y
Reconnect as the user and retry the query:
SQL> CONN test/***
Connected.
SQL> SELECT COUNT(1) FROM FND_USER_RESP_GROUPS_DIRECT;
COUNT(1)
----------
18916
The query now executes successfully.
Key Takeaway
When creating synonyms for editioning views in Oracle E-Business Suite or any EBR-enabled schema, ensure that the referencing user has editioning enabled.
Otherwise, Oracle will fail to translate the synonym, resulting in the ORA-00980 error.
