8 Replies Latest reply: Oct 30, 2009 6:03 AM by Ullhas RSS

    Compiling Public Synonyms

    606583
      I have a number of Public synonyms referencing AWR views such as DBA_HIST_SERVICE_STAT, DBA_HIST_SGASTAT etc which have become invalid.

      I have a user granted DBA privileges which I would like to use to recompile the objects but when I issue an ALTER PUBLIC SYNONYM DBA_HIST_SERVICE_STAT COMPILE; as that user it just returns ORA-01031: insufficient privileges.

      What do I need to grant to the DBA privileged user in order for them to be able to compile the public synonyms?
        • 1. Re: Compiling Public Synonyms
          Eduardo Legatti
          Hi,

          >>as that user it just returns ORA-01031: insufficient privileges.
          Connect to SYS as sysdba ....
          C:\>sqlplus system/pass

          SQL*Plus: Release 10.1.0.2.0 - Production on Mon Jan 28 15:30:43 2008

          Copyright (c) 1982, 2004, Oracle.  All rights reserved.

          Connected to:
          Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
          With the Partitioning, OLAP and Data Mining options

          SYSTEM@ORACLE10> alter public synonym DBA_HIST_SGASTAT compile;
          alter public synonym DBA_HIST_SGASTAT compile
          *
          ERROR at line 1:
          ORA-01031: insufficient privileges

          SYSTEM@ORACLE10> connect sys/pass as sysdba;
          Connected.

          SYS@ORACLE10> alter public synonym DBA_HIST_SGASTAT compile;

          Synonym altered.
          Cheers

          Legatti
          • 2. Re: Compiling Public Synonyms
            57957
            I just want to verify that only the SYS or SYSTEM account can do this by logging in with SYSDBA option. I have been trying to create a script that would cleanup invalid objects after a particular application gets installed but find I cannot seem to clear any of the invalid PUBLIC SYNONYMS although the DBA was able to do it with the SYSTEM account. So my question is, is there a system privilege that can assigned to a schema so that it can recompile public synonyms?
            • 3. Re: Compiling Public Synonyms
              667926
              I have the same question. I want to give other user accounts other than SYS the ability to compile public synonyms. Does anyone have a solution?
              • 4. Re: Compiling Public Synonyms
                Ullhas
                It is not possible for PUBLIC synonyms.
                "ALTER PUBLIC SYNONYM" is an implicit privilege of the SYS user and may not be executed by any other user (not even SYSTEM).
                Also, it is not a valid session privilege and hence can't be granted to any other user.

                It is possible for private synonyms. "CREATE SYNONYM" privilege gives you rights to CREATE/DROP/ALTER private synonyms created by you.

                kallo:DZHLHMC1> sqlplus lhowner/password
                SQL> select * from session_privs where privilege like '%SYNONYM%';

                PRIVILEGE
                ----------------------------------------
                CREATE SYNONYM

                SQL> create synonym test2 for test1;

                Synonym created.

                SQL> alter synonym test2 compile;

                Synonym altered.

                SQL> drop synonym test2;

                Synonym dropped.
                • 5. Re: Compiling Public Synonyms
                  Robert Geier
                  Deleted. Just noticed the age of the OP.
                  • 6. Re: Compiling Public Synonyms
                    Ullhas
                    Hi Robert,

                    I found many invalid PUBLIC synonyms in our DB. I hit this thread during my search and updated with my findings.
                    All my tests were done with 10.2.0.4.0 only. (I removed thee version info to make message short.)
                    We have not applied patch 6725634.
                    However, when I searched for the bug fixes in this patch, I found following:
                    6725634: STBH SYNONYMS ARE INVALIDATED WHEN A TABLE PARTITION IS DROPPED

                    Are you sure this patch solves issue of using "alter public synonym" command?
                    If so, it would be of great help in validating PUBLIC synonyms.
                    Could you please confirm if you also faced similar problem?

                    Cheers,
                    Ullhas
                    • 7. Re: Compiling Public Synonyms
                      Robert Geier
                      The patch should stop the synonyms invalidating. It does not address issues with "alter public synonym".

                      Suggest you raise an SR with Oracle to confirm.
                      • 8. Re: Compiling Public Synonyms
                        Ullhas
                        Thanks for confirmation.
                        I was quite sure this isn't the problem in our case since we do not have any partitioned table.
                        A synonym could become invalid because of many reasons.
                        The most obviously if the object it is referring to is invalid or dropped. (as in our case)
                        In such cases, a developer's obvious wish is to have an ALTER command.
                        But, as of now we stand as it's not possible to compile PUBLIC synonyms by any user other than SYS.
                        And the only way out is re-creating the synonyms.

                        Thanks once again.

                        Cheers,
                        Ullhas