Oracle Database Schedule (SCHEDULER)

In many cases, database administrators or users need to automatically schedule and run many types of jobs, such as performing maintenance work (such as database backups), data loading and validation routines;

Generate reports, collect optimizer statistics, or execute business processes. You can use the Scheduler feature to specify that a task will run at some point in the future. Jobs can be run in a database, on a machine that hosts a database instance, or even on a remote machine.

You can use the scheduler together with the Resource Manager (Resource Manager). The scheduler can activate the Resource Manager program and follow the various resource manager

The priority specified by the consumer group to run the job.

The scheduler was introduced in the Oracle 10g release and was greatly enhanced in the 11g version. Earlier database versions provide job scheduling through the Dbms_job feature. In order to achieve backwards compatibility, the current version still supports this feature, but its functional diversity is far less than the scheduling level.

1, scheduler system structure

The data dictionary contains a table that is the storage point for all dispatcher jobs. You can query this table through the Dba_scheduler_jobs view. The Job Queue Coordinator background Process CJQ0 Monitor This table, starting the job queue process jnnn to run the job as needed. If the CJBQ0 process is started automatically on any defined, active scheduler job. Start the jnnn process as needed, but the maximum number is limited by the job_queue_processes instance parameter, which can be any value of 0-1000 (the default). If you set the value to 0, the tuning will not run.

The Job queue coordinator picks jobs from the job queue table and passes them to the job queue process for execution. It also starts and terminates the job queue process as needed. To view the currently running process, you can query the V$process view.

Select program from V$process where the program is like '%j% ';

A job defined as a procedure runs in the database. Jobs can also be defined as operating system commands or shell scripts: these will run as external operating system tasks. The trigger factor for a job can be a time or event. A time based job may run once or repeatedly depending on the schedule. The job of the base event runs when a specific condition occurs. There are preconfigured events, or you can use user-defined events. Jobs can be linked into chains, using simple rules for branching based on the success of the job.

An advanced feature of the scheduling level is to associate the period with Resource manager. Some jobs may need to run first, for this reason, you can link jobs with resource Manager

Use all groups in order to achieve this goal. You can also use the scheduler to activate the RESOURCE MANAGER plan without needing to change Esource_manager_plan instance parameters or use Dbms_resource_manager. The Switch_plan procedure invokes a manual activation schedule.

You can use a Api-dbms_scheduler package to configure the scheduler, monitor it through a set of data dictionary views, or manage it with database control.

2, Scheduler Object

The most basic object in the Scheduler object is the job. A job can be fully self-contained: it can define the action to be performed and the execution time. In a more advanced configuration, jobs are only part of a structure composed of a large number of various types of scheduler objects.

2.1 Job

The job (Job) specifies the action to be performed and the time it takes to perform the action.

The action to perform can be an anonymous pl/sql block (possibly composed of a single SQL statement), a pl/sql stored procedure (a Java stored procedure or an external procedure can be invoked),

or store any executable file (either a binary executable or a shell script) on the server's file system. A particularly powerful feature (beyond the scope of the OCP course) is a remote external job that runs on a separate machine.

The time to perform an action specifies the timestamp of the startup job and the recurrence interval for future runs, or the triggering event.

Dbms_scheduler. Create_job

2.2 Program

Programs (program) provide an abstraction layer between the job and the action it will perform. Program is to use Dbms_scheduler. Created by the Create_program process.

By taking the contents of a job from a job definition and defining it in a program, you can refer to the same program in different jobs, eliminating the need for multiple definitions to

It is associated with different schedules and job classes. Keep in mind that it is the job that only the enabler can use. Default is not enabled.

2.3 Timetable

A timesheet (schedule) is a specification that specifies the operation time and frequency of a job. The rationale for a schedule is to take the time to perform a job from a job and then associate it with a different

Job Association. The timetable is to use Dbms_scheduler. Create_shedule