Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Compiling different schema Synonym through Procedure

BalajiChellappaSep 20 2006 — edited Sep 20 2006
Hi,
I have created a user with DBA access called DBA_USER.
I want to compile invalid synonym present in another schema through a procedure present in this schema.
What system privilege should I grant for this.
-----------------------------------------------------------------
DBA_USER>
CREATE OR REPLACE PROCEDURE TEST_COMPILE AS
BEGIN
EXECUTE IMMEDIATE 'ALTER SYNONYM SCOTT.EMP_TAB_SYNONYM COMPILE';
END;
/

DBA_USER>EXEC TEST_COMPILE;
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "DBA_USER.TEST_COMPILE", line 3
ORA-06512: at line 1

-----------------------------------------------------------------

Note: I faced same problem with compiling procs/packages. After physically granting
"ALTER ANY PROCEDURE" to DBA_USER it got resolved. I could not able to find a similar one for Synonyms.

Thanks
Balaji Chellappa
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 18 2006
Added on Sep 20 2006
7 comments
1,476 views