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 next message
juniordbanewbie
Messages: 199
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!
Re: Cannot Use DBMS_SCHEDULER.CREATE_PROGRAM even as owner [message #631272 is a reply to message #631269] Mon, 12 January 2015 04:46 Go to previous messageGo to next message
John Watson
Messages: 7255
Registered: January 2010
Location: Global Village
Senior Member
You may need to grant the SCHEDULER_ADMIN role and the MANAGE SCHEDULER privilege. don;t take my word for it, look them up.
Re: Cannot Use DBMS_SCHEDULER.CREATE_PROGRAM even as owner [message #631415 is a reply to message #631272] Tue, 13 January 2015 18:53 Go to previous message
juniordbanewbie
Messages: 199
Registered: April 2014
Senior Member
Hi, I found out I only need the following additional system privilege:


GRANT CREATE JOB TO perfstat;


thanks
Previous Topic: SYS object related query taking huge time
Next Topic: Upgrade Oracle10g to Oracle11g
Goto Forum:
  


Current Time: Sat Feb 17 23:31:44 CST 2018

Total time taken to generate the page: 0.46644 seconds