Home » RDBMS Server » Server Administration » Cannot Use DBMS_SCHEDULER.CREATE_PROGRAM even as owner (11.2.0.3 SE)
Cannot Use DBMS_SCHEDULER.CREATE_PROGRAM even as owner [message #631269] Mon, 12 January 2015 04:40 Go to previous message
juniordbanewbie
Messages: 250
Registered: April 2014
Senior Member
Hi all,

I've confirmed that STATSPACK belongs to PERFSTAT


PERFSTAT@RAC1>SELECT owner, privilege, grantee FROM user_tab_privs WHERE table_name='DBMS_SCHEDULER';

OWNER                          PRIVILEGE
------------------------------ ----------------------------------------
GRANTEE
------------------------------
SYS                            EXECUTE
PERFSTAT



I've also confirmed the execute privilege on both dbms_scheduler and DBMS_ISCHED has been granted explicitly to perfstat



PERFSTAT@RAC1>SELECT owner, privilege, grantee FROM user_tab_privs WHERE table_name='DBMS_SCHEDULER';

OWNER                          PRIVILEGE
------------------------------ ----------------------------------------
GRANTEE
------------------------------
SYS                            EXECUTE
PERFSTAT


PERFSTAT@RAC1>SELECT owner, privilege, grantee FROM user_tab_privs WHERE table_name='DBMS_ISCHED';

OWNER                          PRIVILEGE
------------------------------ ----------------------------------------
GRANTEE
------------------------------
SYS                            EXECUTE
PERFSTAT


when I try to create a program, I cannot, i.e.


  1  BEGIN
  2  DBMS_SCHEDULER.CREATE_PROGRAM (
  3     program_name             =>'statspack_snap',
  4     program_type             =>'PLSQL_BLOCK',
  5     program_action           =>'declare snap number; begin   snap := perfstat.statspack.snap;   end;',
  6     enabled                  =>TRUE,
  7     comments                 =>'Statspack collection');
  8* END;
  9  /
BEGIN
*
ERROR at line 1:
ORA-27486: insufficient privileges
ORA-06512: at "SYS.DBMS_ISCHED", line 5
ORA-06512: at "SYS.DBMS_SCHEDULER", line 36
ORA-06512: at line 2


but when I run the code as sysdba

it works

SYS@RAC1>ed
Wrote file afiedt.buf

  1  BEGIN
  2  DBMS_SCHEDULER.CREATE_PROGRAM (
  3     program_name             =>'statspack_snap',
  4     program_type             =>'PLSQL_BLOCK',
  5     program_action           =>'declare snap number; begin   snap := perfsta
t.statspack.snap;   end;',
  6     enabled                  =>TRUE,
  7     comments                 =>'Statspack collection');
  8* END;
  9  /

PL/SQL procedure successfully completed.




what am I missing that I cannot execute as perfstat?

thanks a lot!
 
Read Message
Read Message
Read Message
Previous Topic: SYS object related query taking huge time
Next Topic: Upgrade Oracle10g to Oracle11g
Goto Forum:
  


Current Time: Fri Mar 29 09:18:31 CDT 2024