Wednesday, May 27, 2015

DBMS SCHEDULER MAINTENANCE

Create a job

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
     job_name           => 'TEST_JOB',
     job_type           => 'EXECUTABLE',
     job_action         => '/u01/app/abc.sql',
     repeat_interval    => 'FREQ=MINUTELY',
     enabled            => TRUE
  );
END;
/


Unlike DBMS_JOB you do not need to commit the job creation for it to be taken into account.
As a corollary, if you want to cancel it, you have to remove or disable it.

           ******   Remove a job

EXEC DBMS_SCHEDULER.DROP_JOB('TEST_JOB');

           ******   Run a job now

To force immediate job execution:
EXEC dbms_scheduler.run_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 = 'TEST_JOB';
SELECT * FROM dba_scheduler_job_log WHERE job_name = 'TEST_JOB';

           ******    or checking from JOB owner schema

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

No comments :

Post a Comment