High Availbility

OS & Virtualization

Saturday, January 28, 2012

DBMS SCHEDULER

DBMS SCHEDULER is a more sophisticated job scheduler introduced in Oracle 10g. The older job scheduler, DBMS_JOB, is still available, is easier to use in simple cases
Make sure the OracleJobScheduler Service is started.
Create a job
BEGIN
   DBMS_SCHEDULER.CREATE_JOB (
   job_name => 'myjob',
   job_type => 'EXECUTABLE',
   job_action => 'd:\oracle\script\vng.bat',
   repeat_interval => 'FREQ=MINUTELY',
   enabled => TRUE );
END;

Remove a job
EXEC DBMS_SCHEDULER.DROP_JOB('myjob');
Change job attributes
Examples:
EXEC DBMS_SCHEDULER.SET_ATTRIBUTE('WEEKNIGHT_WINDOW', 'duration', '+000 06:00:00');
BEGIN
   DBMS_SCHEDULER.SET_ATTRIBUTE ('WEEKNIGHT_WINDOW',    'repeat_interval', 'freq=daily;byday=MON, TUE, WED, THU, FRI;byhour=0;byminute=0;bysecond=0');
END;/
Enable
/
Disable a job
BEGIN
   DBMS_SCHEDULER.ENABLE('myjob');
END;

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

Monitoring jobs
SELECT * FROM dba_scheduler_jobs WHERE job_name = 'myjob';
SELECT * FROM dba_scheduler_job_log WHERE job_name = 'myjob';

Use user_scheduler_jobs and user_scheduler_job_log to only see jobs that belong to your user (current schema).

No comments: