I am currrntly using this to collect stats every hour each day:-
variable jobno number;
variable instno number;
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);
Can anyone help me modify this job so that it runs weekly instead of daily
try 'sysdate+7' instead of 'sysdate+1/24'
Joseph R.P. Maloney, CSP,CDP,CCP
'The answer is 42'
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?
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.
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.
if runday or (runday-15 minutes) --you can code this
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.
Click Here to Expand Forum to Full Width