Compiling different schema Synonym through Procedure
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