• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

ORA-01031 When Compiling A Synonym

by admin

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.

Filed Under: oracle

Some more articles you might also be interested in …

  1. Oracle Database – How to recover from a lost datafile with no backup
  2. Beginners Guide to Oracle Database Vault
  3. Oracle GoldenGate: Replicat Sample Parameter File
  4. Error: ORA-16810: multiple errors or warnings detected for the database
  5. How To Create Device Alias For ASM Disks Using mknod On Linux/Unix
  6. Oracle 12c Multitenant Architecture: Troubleshooting Ora-12518
  7. Oracle RAC: How to Change private network interface name, subnet or netmask
  8. What Is Oracle Key Vault
  9. Unable to Drop Undo tablespace Since Undo Segment is in Needs Recovery
  10. Undo Modes in 12.2 Multitenant Databases – Local and Shared Modes

You May Also Like

Primary Sidebar

Recent Posts

  • vgextend Command Examples in Linux
  • setpci command – configure PCI device
  • db_load command – generate db database
  • bsdtar command – Read and write tape archive files

© 2022 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright