ORA-01031 When Compiling A Synonym

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.