running a JOB in queue
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: running a JOB in queue

  1. #1
    Join Date
    Mar 2001
    Posts
    286
    I submit a job by doing this:

    VARIABLE jobno number;
    BEGIN
    DBMS_JOB.SUBMIT(:jobno,
    'DBMS_STATS.GATHER_DATABASE_STATS;',
    sysdate, 'SYSDATE + 1',
    TRUE, 1, FALSE);
    commit;
    END;
    /

    Now, I am getting this:

    ORA-12012: error on auto execute of job 22
    ORA-20000: Insufficient privileges to analyze an object in Database
    ORA-06512: at "SYS.DBMS_STATS", line 4621
    ORA-06512: at "SYS.DBMS_STATS", line 4756
    ORA-06512: at "SYS.DBMS_STATS", line 4741

    Please note: I am logging in as a user with dba role. I can run "exec 'DBMS_STATS.GATHER_DATABASE_STATS;" without any problem. Why cannot I run it in a job?



  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by dba_admin
    Please note: I am logging in as a user with dba role. I can run "exec 'DBMS_STATS.GATHER_DATABASE_STATS;" without any problem. Why cannot I run it in a job?
    Because inside PL/SQL your roles are disabled. In SQL*Plus do the following:
    - Log in as that DBA user
    - Isue: SET ROLE NONE;
    - Isue: exec 'DBMS_STATS.GATHER_DATABASE_STATS;
    This is exactly what happens when you switch your executing environment into PL/SQL. And you'll see you'll get the same "insufficient privileges" error message.

    You need an explicit priviledge on that package, not the one obtained through role. Either let SYS grant you "EXECUTE ON DBMS_STATS" or let some DBA user grant you "EXECUTE ANY PROCEDURE" and your job will run with no problems.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Mar 2001
    Posts
    286
    Simply

    Grant analyze any to the_user;

    will do it.

    Thank you, anyway.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width