This content has been marked as final. Show 8 replies
>>as that user it just returns ORA-01031: insufficient privileges.
Connect to SYS as sysdba ....
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.
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;
SYS@ORACLE10> alter public synonym DBA_HIST_SGASTAT compile;
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?
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%';
SQL> create synonym test2 for test1;
SQL> alter synonym test2 compile;
SQL> drop synonym test2;
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?
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.