OraFAQ Forum: Server Administration » Cannot Use DBMS_SCHEDULER.CREATE

Home » RDBMS Server » Server Administration » Cannot Use DBMS_SCHEDULER.CREATE_PROGRAM even as owner (11.2.0.3 SE)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Cannot Use DBMS_SCHEDULER.CREATE_PROGRAM even as owner [message #631269]

Mon, 12 January 2015 04:40

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!

Report message to a moderator