Scheduler Architecture
The
Scheduler architecture consists of the job table, job coordinator, and the job
workers (or slaves, as Oracle calls them). The job table contains information
about jobs, such as the job name, program name, and job owner. You can examine
the job table by using the DBA_SCHEDULER_JOBS view. The job coordinator
regularly looks in the job table to find out what jobs to execute. The job
coordinator creates and manages the job worker processes, which actually
execute the job. When you create a new job or execute a job, a background
process (cjqnnn) wakes up and coordinates the running of the job. When the job
coordinator tells a job worker to execute a job, the worker process starts a
new database session and starts a transaction. It executes the job, and once
completed, it commits and ends the transaction and terminates the database
session. The job worker updates the job table, the run count, and the job log
table.
Scheduler Privileges:
Oracle
creates all jobs, programs, and schedules in the schema of the user that
creates these objects, but it creates all the advanced Scheduler components,
like job classes, windows, and window groups, at the database level, and their
owner is the SYS schema. The SCHEDULER_ADMIN role contains all Scheduler system
privileges, with the WITH ADMIN OPTION clause. The DBA role contains the
SCHEDULER_ADMIN role. The MANAGE SCHEDULER system privilege lets you do the
following:
•
Create, drop, and alter job classes, windows, and window groups. • Stop any
job. • Start and stop windows prematurely.
Note: All Scheduler objects are of the form
[schema.]name. By default, all scheduler object names are in uppercase, unless
you wrap the lowercase names in double quotes, as in "test_job".
You
must have the CREATE JOB privilege to create Scheduler components (jobs,
schedules, programs, chains, and events). To use the advanced Scheduler
components (windows, window groups, and job classes), you need the MANAGE
SCHEDULER system privilege. You can assign other users the right to use one of
your components by giving them EXECUTE privileges on that component:
• The
EXECUTE ANY PROGRAM privilege lets a user execute any program under any schema.
• The
EXECUTE ANY CLASS privilege lets you assign a job to any job class.
In
order for users to modify Scheduler components, they must use the GRANT ALTER
SQL statement for each Scheduler component.
Note
: To be able to create a job in a job class you generate, you must have a
separate EXECUTE privilege on that job class.
Note the following basic points regarding Scheduler privileges:
• To
create a job, you must have the CREATE JOB privilege. • You don’t need any
special privileges to specify a schedule, a window or window group, or a
program that you own.
• If
you specify a program owned by a different user, you must have the EXECUTE
privilege on that program, or the EXECUTE ANY PROGRAM system privilege.
Eg.
Creating a Basic Scheduler Job Without a Program or Schedule:
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
JOB_NAME
=> 'test_job',
JOB_TYPE
=> 'PLSQL_BLOCK',
JOB_ACTION
=> 'insert into
ashwani.suppliers select * from ashwani.suppliers;',
START_DATE
=> '11-APRIL-2016 06.30.00
PM',
repeat_interval
=> 'FREQ=MINUTELY;INTERVAL=1',
END_DATE
=> '20-APRIL-2016 07.00.00
PM',
COMMENTS
=> 'Insert new suppliers into
the suppliers table',
ENABLED
=> TRUE);
END;
/
Query to find out the details about TEST_JOB jobs in your database:
SQL>SELECT job_name, program_name FROM
dba_scheduler_jobs WHERE job_style='TEST_JOB'
Command for disabling scheduler:
EXEC DBMS_SCHEDULER.DISABLE(name =>
'"SYS"."TEST_JOB"');
Command for enabling scheduler:
EXEC DBMS_SCHEDULER.ENABLE(name => '"SYS"."TEST_JOB"');
Command for running job manually:
EXEC DBMS_SCHEDULER.RUN_JOB('"SYS"."TEST_JOB"');
