dbms_job

[itwizzkid]

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

[jrpm]

try 'sysdate+7' instead of 'sysdate+1/24'

[itwizzkid]

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

[dantheman]

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.