Oracle Database Discussions

Hello All,

Oracle 11g standard edition 11.2.0.1

I want to take statspack snap twice a day. i.e

Morning 9AM to 10AM
Afternoon 14:00 to 15:00

I want this to be happened automatically.
To achieve this I performed the following operations.

1. I create a function NEXTRUNTIME

CREATE OR REPLACE FUNCTION NextRunTime RETURN DATE as
    nextDate DATE;
    nowDate DATE;
BEGIN
-- get the current date/time
    nowDate := SYSDATE;
-- every fifteen minutes
if((to_char(nowDate, 'HH24') > 9 and to_char(nowDate, 'HH24') < 10) or 
   (to_char(nowDate, 'HH24') > 14 and to_char(nowDate, 'HH24') < 15)) then
nextDate := nowDate  + (15/1440);
elsif (to_char(nowDate, 'HH24') > 10 and to_char(nowDate, 'HH24') < 14) then
nextDate := SYSDATE;
else
nextDate := nowDate + 18/24;
end if;
return nextDate;
end;
/

HOW TO PUT THE NEXT DAY SCHEDULE IN ELSE PART (i.e for 9AM and 2PM)

2. I scheduled the statspack job by calling the above function.

variable jobno number;
variable instno number;
BEGIN
SELECT instance_number INTO :instno FROM v$instance;
-- --------------------------------------------------------------------------------
-- Submit job to begin at 9 am and run every 15 minutes
-- ---------------------------------------------------------------------------------
    dbms_job.submit(
    :jobno,
    'statspack.snap;',
    to_date('03/oct/12 09:00:00', 'dd/mon/yy HH24:MI:SS'),
    'NextRunTime',
    TRUE,
    :instno);
-- -----------------------------------------------------------------------------
-- Submit job to begin at 1400 and run every 15 minutes
-- ------------------------------------------------------------------------------
    dbms_job.submit(
    :jobno,
    'statspack.snap;',
    to_date('03/oct/12 14:00:00', 'dd/mon/yy HH24:MI:SS'),
    'NextRunTime',
    TRUE,
    :instno);
    COMMIT;
END;
/

But, I am confused with the timing.
My statspack is running every 15 minutes, in other times also (other than scheduled time).
I also queried the below command, which resulted in "no row selected"

SQL>SELECT JOB, SUBSTR(WHAT,1,35), NEXT_DATE, NEXT_SEC, INTERVAL FROM DBA_JOBS;
no rows selected

Can you please help me to schedule statspack snap only during 9:00-10:00 and 14:00 to 15:00 every day.


Thanks,
Madhu

Edited by: Madhu.149 on Oct 3, 2012 5:51 PM

Edited by: Madhu.149 on Oct 3, 2012 5:56 PM

Edited by: Madhu.149 on Oct 3, 2012 5:59 PM