06 January 2013

All about oracle Jobs

This is how a basic job is written :

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'myJob', 
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN procedure_name ; END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=hourly; byminute=0; bysecond=0;',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'Job defined entirely by the CREATE JOB procedure.');
END;

Note :  Other types of repeat_interval 
repeat_interval => 'freq=weekly; BYDAY=sun; byhour=8; byminute=0',
repeat_interval  =>  'FREQ=SECONDLY;INTERVAL=10',



This is how job details can be seen :

SELECT * FROM dba_scheduler_jobs;
 
Stopping job :
 
BEGIN
DBMS_SCHEDULER.STOP_JOB('procedure_name,job_class');  
END;
  
Job class can been seen from "dba_scheduler_jobs"  table.

For dropping job use  => DBMS_SCHEDULER.DROP_JOB('procedure_name,job_class');

For disabling job use => DBMS_SCHEDULER.DISABLE('procedure_name,job_class');
For enabling job use => DBMS_SCHEDULER.ENABLE ('procedure_name,job_class'); 





for more info use links : 

Link1
Link2




Delicious add to del.icio.us saved by 0 users