dbms_job
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: dbms_job

  1. #1
    Join Date
    Feb 2001
    Posts
    107
    I am currrntly using this to collect stats every hour each day:-

    variable jobno number;
    variable instno number;
    begin
    select instance_number into :instno from v$instance;
    dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);
    commit;
    end;
    /

    Can anyone help me modify this job so that it runs weekly instead of daily


    Thanks

  2. #2
    Join Date
    Apr 2001
    Location
    Louisville KY
    Posts
    295
    try 'sysdate+7' instead of 'sysdate+1/24'
    Joseph R.P. Maloney, CSP,CDP,CCP
    'The answer is 42'

  3. #3
    Join Date
    Feb 2001
    Posts
    107
    jrpm,

    Thanks for the quick reply. Will this just run the report once every week?. I need to run this once every week in a 24 hour period collecting stats at intervals of 30 mins

    Has anyone else got any ideas?


    Thanks

  4. #4
    Join Date
    Aug 2002
    Posts
    1

    Lightbulb Try this to turn your dbms_job on/off

    What I would suggest is to create a procedure that is used to break/unbreak the job, and create a job that calls the procedure at the right times.

    Try this:
    1. Create a procedure (call it toggle) that checks the day. If it is 'run day' then change the dbms_job status to unbroken. If not, make sure the the dbms_job status is broken.

    BEGIN

    if runday or (runday-15 minutes) --you can code this
    DBMS_JOB.BROKEN('101',TRUE);
    else
    DBMS_JOB.BROKEN('101',FALSE);
    end if:

    This procedure will toggle the stats job status.

    2. Create another DBMS_JOB that has an interval of daily (overkill) that has a next_date that is ~ 15 minutes before your stats job. In the new job, call the stored procedure (toggle).

    That way, you have a job that is broken 6 days a week on purpose, and an unbroken job that 'fixes' the broken job when necessary. The stats is run for the day at half hour intervals on 'runday', then 15 minutes before it is to run on the following day, the new job 'breaks' it. It stays that way up until 15 minutes before it is to run on 'runday', at which point it is 'fixed'.

    Hope this helps.

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