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

The Geek Diary

CONCEPTS | BASICS | HOWTO

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • Linux Services
    • VCS
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
    • Data Guard
  • DevOps
    • Docker
    • Shell Scripting
  • Interview Questions
  • 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. How to limit access to oracle database so that only 1 user per schema is connected ( 1 Concurrent user per schema)
  2. How to Create Undo Tablespace for a Newly Added RAC Instance (ORA-30012)
  3. Oracle Database : How to set Environment Variables Using Srvctl
  4. How To Automate The Opening Of Pluggable Databases After The CDB Starts Up in Oracle 12c
  5. How To Change Timezone for Oracle Grid Infrastructure
  6. Understanding Oracle Database Recyclebin Features and How to Disable it
  7. Adding Users to Oracle Passwordfile (Oracle Passwordfile Authentication)
  8. What is OCFS or OCFS2
  9. Oracle ASM 12c – New Features with examples
  10. What are Oracle Database Valut Schemas

You May Also Like

Primary Sidebar

Recent Posts

  • How to disable ACPI in CentOS/RHEL 7
  • How to Use real-time query to access data on a physical standby database
  • CentOS/RHEL 8: “ACPI MEMORY OR I/O RESET_REG” Server Hung after reboot
  • How to Create a Physical Standby Database by Using SQL and RMAN Commands
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary