Oracle Tips

scheduler job status:
select
    d.job_name,
    d.job_action 
from
    dba_scheduler_jobs d,
    sys.scheduler$_job s 
where
    d.job_action = s.program_action 
and
    s.obj# = &myjobnum;



The following can be used to create a job using a predefined Program and Schedule:


BEGIN
DBMS_SCHEDULER.CREATE_JOB
(
 job_name                    => `BACKUP_JOB_01`,
 program_name            => `BACKUP_PROGRAM`,
 schedule_name            => `BACKUP_SCHEDULE`);
END;
/


Example 2:


The following can be used to create a job using a predefined Program without a predefined Schedule:


BEGIN
DBMS_SCHEDULER.CREATE_JOB
(
 job_name                    => `BACKUP_JOB_02`,
 program_name            => `BACKUP_PROGRAM`,
 start_date        => `TRUNC(SYSDATE)+23/24`,
 repeat_interval => `FREQ=WEEKLY; BYDAY=SUN` );
END;
/


Example 3:


The following can be used to create a job using a predefined Schedule without a predefined Program:


BEGIN
DBMS_SCHEDULER.CREATE_JOB
(
 job_name                    => `BACKUP_JOB_03`,
 schedule_name            => `BACKUP_SCHEDULE`,
 job_type                      => `EXECUTABLE`,
 job_action       => `/dba/scripts/weekly_backup.sh`
);
END;
/


Example 4:


The following can be used to create a job without a predefined Program and Schedule:


BEGIN
DBMS_SCHEDULER.CREATE_JOB
(
 job_name                    => `BACKUP_JOB_04`,
 job_type                      => `EXECUTABLE`,
 job_action       => `/dba/scripts/weekly_backup.sh`,
 start_date        => `TRUNC(SYSDATE)+23/24`
 repeat_interval => `FREQ=WEEKLY; BYDAY=SUN`
);
END;
/


The following is the syntax to run, stop, copy, and drop a job:


DBMS_SCHEDULER.RUN_JOB
(  job_name                  in varchar2 );
DBMS_SCHEDULER.STOP_JOB
( job_name                   in varchar2,
 force               in Boolean default false );


The copy_job procedure copies all attributes of an existing job to a new job.


DBMS_SCHEDULER.COPY_JOB
(
 old_job                       in varchar2,
 new_job                      in varchar2);


DBMS_SCHEDULER.DROP_JOB
(
 job_name                    in varchar2,
 force               in Boolean default false
);


Create Job Class


A Job Class defines a category of jobs that share common resource usage requirements.  A Job Class is associated with two attributes: the resource consumer group, which defines a set of user sessions that have common resource processing requirements; and a database service name, which defines the instance to which the job class belongs.  Each job belongs to a single job class at any given time.  By associating a Job with a Job Class, the amount of resources a Job can use during its execution can be managed.


The syntax to create a Job Class is:


DBMS_SCHEDULER.CREATE_JOB_CLASS
(
 job_class_name                       in varchar2,
 resource_consumer_group       in varchar2 default null,
 service                         in varchar2 default null,
 log_purge_policy                     in varchar2 default null,
 comments                                in varchar2 default null
);


By default, the Scheduler log table entries are not purged. The log_purge_policy defines the policy for purging the log table entries.


Data Dictionary Views


The following SQL script provides a list of data dictionary views used to monitor the Scheduler’s activities:


SQL> select table_name, comments
   2 from   dict
   3 where table_name like 'DBA%SCHEDULER%'
   4 order by table_name;