The Problem
When any user being granted the DBA role tries to compile synonyms of other users it will get the following errors:
SQL> conn system/xxxxx Connected. SQL> create synonym user1.emps for user1.emp; Synonym created.
SQL> alter synonym user1.emps compile; alter synonym user1.emps compile * ERROR at line 1: ORA-01031: insufficient privileges
The synonyms can be compiled by SYS or by the synonyms’ owners:
SQL> conn / as sysdba Connected. SQL> alter synonym user1.emps compile; Synonym altered.
SQL> conn user1/xxxxx Connected. SQL> alter synonym user1.emps compile; Synonym altered.
The Solution
This is the expected behavior. The command “alter synonym [name] compile” is not documented and as such, it is not supported. Although it works in some circumstances it is not meant to be used by the users directly.
This is the expected behavior. If all the synonyms have to compiled then run the recompilation script as SYS.