DBMS_JOBS DOES NOT WORK
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 26

Thread: DBMS_JOBS DOES NOT WORK

  1. #1
    Join Date
    Sep 2002
    Posts
    411
    Hi all,

    I have the follow job will be executed as follow:

    var j number
    begin
    dbms_job.submit
    (
    :j,
    'SYS.DBMS_UTILITY.ANALYZE_SCHEMA(''TEST'',''COMPUTE'');
    ',sysdate,'sysdate+(90/(24*60*60))'
    );
    COMMIT;
    end;


    THIS Job suppose to start every 90 sec but for some reason, it doesn't start at all.

    I did check the DBA_JOBS and the job is there. Any suggestions

  2. #2
    Join Date
    Oct 2000
    Posts
    467
    what abt the job_queue_processes and job_queue_interval parameters. Have u set them up ?
    Secondly, is the job broken ?. Check out broken, failures for the job and the next_sec, next_date from dba_jobs.
    Broken should be 'N' and failures should be 0.

    Vinit

  3. #3
    Join Date
    Oct 2000
    Posts
    467
    Hold on..why would you want to analyze - compute ur schema every 90 seconds ??
    Vinit

  4. #4
    Join Date
    Aug 2002
    Location
    Bangalore, India
    Posts
    405
    Probably he is experimenting with a homemade database
    -nagarjuna

  5. #5
    Join Date
    Sep 2002
    Posts
    411
    this is only a test so that I set it to run every 90 second to be sure it it's working.


    ANY WAY, IT'S NOT WORKING YET.

    I HAVE SET JOB_QUEUE_PROCESS=10 AND JOB_QUEUE_INTERVAL=60, When I look into DBA_JOBS, I got the next time it will be executed however, on FAILURES=2 , what does it mean???

    any advises???



    [Edited by mike2000 on 10-01-2002 at 03:48 PM]

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    look trace files in bdump

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by mike2000
    When I look into DBA_JOBS, I got the next time it will be executed however, on FAILURES=2 , what does it mean???
    It means your job has allready run for two times and both times it was terminated because of some unhandled error. It will continue to resubmit itself after each failure (up to 16 times), each time with larger pause periode.

    I'm almost certain the cause of the error has something to do with privileges of the owner of the job. Try the following, connected as the same user as the one who ownes the job:

    BEGIN
    SYS.DBMS_UTILITY.ANALYZE_SCHEMA('TEST','COMPUTE');
    END;

    If this reports any ORA-error, just fix it and your job will run fine.

    My wild guess is that your user does not have direct execute priviledge to run that package. If that is so, you'll need to grant him:

    GRANT EXECUTE ON sys.dbms_utility TO that_user;
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Sep 2002
    Posts
    411
    Mate,

    thanks all, this is what I have :

    I did have the proper grant to the user who run the job to SYS.DBMS_UTILITY and when I execute the below it ran fine:

    BEGIN
    SYS.DBMS_UTILITY.ANALYZE_SCHEMA('TEST','COMPUTE');
    END;


    but when I put it in the DBMS_JOB to run, it failed and I log into the log UNSUFFICIENT PRIVILEGE on execute SYS.DBMS_UTILITY.

    I am getting closer but I couldn't figure it out what's going on.

    any ideas???



  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    you have to do direct grant of execute priv on dbms_utility to the user if you want to use the package inside PL/SQL (stored procedure)

  10. #10
    Join Date
    Sep 2002
    Posts
    411
    Pando,
    thanks for your prompt reply.

    I did grant the users who owned this job with EXECUTE to SYS.DBMS_UTILITY already so that when I run it manually it work.

    when you mean I have to direct grant to execute on DBMS_UTILITY, you mean I have to log in as SYS and run this query again:
    GRANT EXECUTE ON SYS.DBMS_UTILITY TO TEST.

    is that what you mean???

    thanks Pando


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