Create and Drop a Job SCHEDULER (DBMS_SCHEDULER AND DBMS_JOB) IN ORACLE DATABASE 10G

create  table honnikery as select * from emp where 1=2


select * from honnikery

select job,last_date,next_date,broken from dba_jobs



CREATE OR REPLACE PROCEDURE SCHEDULER_TEST
AS
BEGIN
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE honnikery';
END;
BEGIN
INSERT INTO honnikery
SELECT * FROM emp;
COMMIT;
END;
END;

declare

  l_jobno number;

begin

 dbms_job.submit(

  l_jobno,

   what=>'Proc_Test;',

   next_date=>sysdate, -- start now

   interval=>'sysdate+5/(24*60)'

 ); 

 commit;

end;

BEGIN 

  DBMS_SCHEDULER.create_job ( 

  job_name => 'SCHEDULER_JOB',

   job_type => 'PLSQL_BLOCK', 

   job_action => 'BEGIN SCHEDULER_TEST ;  END;', 

   start_date => SYSTIMESTAMP,

    repeat_interval => 'freq=hourly; byminute=5; bysecond=0;', 

    enabled => TRUE); 

    END;

  Drop a Job SCHEDULER

begin
dbms_scheduler.drop_job(job_name => 'SCHEDULER_JOB');
end;

Enable / Disable a job

BEGIN 
  DBMS_SCHEDULER.DISABLE('SCHEDULER_JOB');
END;

BEGIN 
  DBMS_SCHEDULER.ENABLE('SCHEDULER_JOB');
END;

Monitoring jobs 

 SELECT * FROM user_scheduler_jobs WHERE job_name = 'SCHEDULER_JOB';
 SELECT * FROM user_scheduler_job_log WHERE job_name = 'SCHEDULER_JOB';