2016-02-24

Compiling public synonyms

Environment: Oracle database 11.2, Toad for Oracle 12.6.0.53

There is an invalid public synonym F_GET_PARTY_NAME. The target object exists and valid.
select s.* , o.*
--'alter public synonym ' || synonym_name || ' compile;'
from dba_synonyms s, dba_objects o
where s.owner=o.owner and s.synonym_name=O.OBJECT_NAME
and o.status ='INVALID' 
and s.owner='PUBLIC'
;
When connected as SYSTEM or SYS in “TOAD Script runner” or in “TOAD for Oracle 12.6.0.53” (looks like a TOAD’s bug)
alter public synonym F_GET_PARTY_NAME compile;

alter public synonym
Error at line 1
ORA-00995: missing or invalid synonym identifier

F_GET_PARTY_NAME compile;
Error at line 1
ORA-00900: invalid SQL statement

When connected as SYSTEM in sqlplus
alter public synonym F_GET_PARTY_NAME compile;
alter public synonym F_GET_PARTY_NAME compile
*
ERROR at line 1:
ORA-01031: insufficient privileges

When connected as SYS in sqlplus
SQL> alter public synonym F_GET_PARTY_NAME compile;

Synonym altered.