install statspack for snap every 15 mins and snap for 2 hours between special window

Hi All,

Here is the requirement to configure statspack.

install statspackto take performance snapshots every 15 minutes on the 15 minutes except during our batch window of midnight until 7am, during which time we would like performance snapshots taken every 2 hours at the top of the hour (midnight, 2, 4 and 6).



To acheive the requirement we need to create function in which we will pass the data and time values. Same function will be used when we submit dbms_job.
1>Create a funciton.
CREATE OR REPLACE FUNCTION NEXTRUNRETURN DATE as NEXT_DATE DATE; CURRENT_DATE DATE;BEGIN CURRENT_DATE := SYSDATE; if (to_char(CURRENT_DATE,'HH24') > 7 and to_char(CURRENT_DATE,'HH24') <> 0 and to_char(CURRENT_DATE,'HH24') <> Use function NEXTRUN while submitting DBMS_JOB procedure.
variable JOB_NO number;variable INST_NO number;
DECLARE JOB_NO number(6);BEGINSELECT instance_number INTO :INST_NO FROM v$instance;
dbms_job.submit( :JOB_NO, 'statspack.snap;', to_date('03/MAR/11 00:00:00', 'dd/mon/yy HH24:MI:SS'), 'NEXTRUN', TRUE, :INST_NO);
dbms_job.submit( :JOB_NO, 'statspack.snap;', to_date('03/MAR/11 07:00:00', 'dd/mon/yy HH24:MI:SS'), 'NEXTRUN', TRUE, :INST_NO); COMMIT;END;





3>To verify the same next day.



Column Job# format a4Column What format a27Column Last_Date format a15Column Next_Date format a15select substr(to_char(job),1,3) Job#, substr(what,1,27) What, to_char(last_date, 'dd-MON-YY HH24:MI') Last_Date, to_char(Next_Date, 'dd-MON-YY HH24:MI') Next_Date, failures fail#, total_timefrom user_jobs;



Regards

Guru