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
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.
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 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
Each schedular component is stored in database schema
Privilege for schedular component
Creation a job
repeat_interval=>'trun(sysdate +1) + 23/24',
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
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;
for enable logging for a job