Database Technologies‎ > ‎Oracle‎ > ‎

Scheduling jobs ../ in progress

Job Queue processes executes the submitted jobs, this processes are spawned
by background process (CJQ0).
Number of process to spawn are specified in the init parameter
JOB_QUEUE_PROCESSES.
If the above is 0 no jobs will be executed as no job queue process is spawned.
to change the parameter.

ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 10;

Jobs are submitted using the DBMS_JOB package.
 
 Oracle 9i
 
Detailed Description of DBA_JOBS View
 
JOB                             Job Id
LOG_USER USER        Who was submitted the job
PRIV_USER  USER      Who's privileges apply to this job"
SCHEMA_USER          Current_schema in which job will operate
LAST_DATE                Date that this job last successfully executed
LAST_SEC                  This is when the last successful execution started."
THIS_DATE                 Date that this job started executing, NULL when its not executing
THIS_SEC TIME          Time
NEXT_DATE                Next when the job is scheduled for execution
NEXT_SEC                  Timing of the next execution
TOTAL_TIME               Total time spent by the system on this job, in seconds"
BROKEN                     Description of DBA_JOBS.BROKEN: "If Y, no attempt is being made to run this job. See dbms_jobq.broken(job)."
INTERVAL                   Interval in terms of Date Function
FAILURES                   Number of times this job started and failed since its last success?"
WHAT                         PL/SQL block, sp,trigger that this job executes"
NLS_ENV                    "Session parameters" alter session parameters describing the NLS environment of the job"
INSTANCE                   "Instance number to run the job


 Oracle 10g

Oracle 10g schedular is more sophisticated compared to earlier releases

DBMS_SCHEDULAR packages contains all the functions and procedure to
deal with schedular

Three component of schedular

JOB       Program that is to be run and the time for its       execution
SCHEDULE  when the job is to be run and the number of times it  has to be run
          , you could use schedular to use above schedule for  multiple job
program   scripts,procedure,executables


=
Each schedular component is stored in database schema

==

Privilege for schedular component

creation
usage
administrative

Creation a job

SQL>Begin
    dbms_schedular.create_job(
    job_name=>'SCOTT.DB_BACKUP',
    job_type=>'executable',
    job_action=>'/path/to/bkp_script.sh';,
    start_date=>trun(sysdate) +23/24',
    repeat_interval=>'trun(sysdate +1) + 23/24',
    comment=>'Night backup');
   END;
/


The user in whose schema job
is created is the job owner,
and job are executed with priv
of the job owner,

job_type = 1) plsql_block
           2) stored_procedure
           3) executable




repeat_interval=>'FREQ=HOURLY;INTERVAL=4'
repeat_interval=>'FREQ=DAILY'
repeat_interval=>'FREQ=MINUTELY;INTERVAL=15'

execute dbms_schedular.run_job('SCOTT.STATS_JOB');
execute dbms_schedular.stop_job('SCOTT.STATS_JOB',force=>TRUE);   force = signal error and stop
execute dbms_schedular.DROP_job('SCOTT.STATS_JOB',force=>TRUE);   force = stop running instance and drip

select * from dba_schedular_jobs;
select * from dba_schedular_running_jobs;
select * from dba_schedular_job_args;
select * from dba_schedular_programs;

select job_name,status,error#,run_duration
from dba_schedular_job_run_details

select job_name,operation,owner
from dba_schedular_job_log;


for enable logging for a job

execute dbms_schedular.set_attribute('vmstat_job',-'logging_level',dbms_schedular.logging_full);


Comments