Scheduler Architecture

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