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);