Oracle DB uses the scheduler to automate tasks ____oracle

• Use schedulers to simplify administrative tasks

• Create jobs, programs, and schedules • Monitor Job execution

• Use a time based or event-based schedule to perform a scheduler job

• Describe the purpose of Windows, window groups, job classes, and consumer groups

• Use email notification

• Use the job chain to perform a series of related tasks

• Describes scheduler jobs on remote systems

• Use advanced Scheduler concepts to prioritize job priorities simplify management tasks

Many tasks in an Oracle environment require job scheduling capabilities. Routine database maintenance and application logic require scheduling and running jobs on a regular basis. Enterprise to Enterprise (business-to-business) applications require scheduling of their business events. The DBA needs to schedule regular maintenance jobs in the specified window of time.

Oracle DB provides an advanced scheduling capability through the database scheduler, which is a collection of functions and procedures in the Dbms_scheduler package. This scheduler can be invoked in any SQL environment, or through Oracle Enterprise Manager (EM).

With this scheduler, database administrators and application developers can control the time and location of performing various tasks in a database environment. These tasks may be time-consuming and complex, and you can use the scheduler to manage and schedule these tasks. You can start a scheduler job based on time or when a specified event occurs, and the scheduler can also raise events when the job status changes (for example, from running to complete). You can also use a series of named programs that are linked together to achieve a group goal. Core Components

Core components and major steps

A job contains two required components: the action that needs to be performed, the time that the operation occurred, or the schedule. Actions are represented by the Job_type and job_action parameters in the command area and job properties. "Time" is represented in the schedule, and the schedule can be based on time or events, or on the results of other jobs.

The scheduler uses the following basic components:

• "Job" Specifies the action to be performed. It can be a pl/sql process, a pure binary executable, a Java application, or a shell script. You can specify the program (content) and schedule (time) as part of the job definition.

You can also use an existing program or schedule instead. You can use the parameters of a job to customize its run-time behavior.

• "Schedule" specifies the time and number of times the job should be executed. Scheduling can be based on time or events. You can define a schedule for a job by using a series of dates, an event, or a combination of both, as well as an additional representation of the repeat interval

Ming. You can store the schedule of a job separately, and then use the same schedule for multiple jobs.

• "Program" is a collection of metadata about a specific executable file, script, or procedure. An automatic job will perform a task. Using a program, you can modify a job task or "content" without modifying the job itself. You can define the parameters of a program so that users can modify the run-time behavior of the task. Basic Workflows

Use the scheduler to simplify administrative tasks:

1. Create a program (enabled or disabled)-optional

– Reuse this action in multiple jobs

– Change the schedule of a job without recreating the pl/sql block

2. Create and use scheduling

3. Create and submit jobs

All steps can be performed in the graphical environment of Oracle Enterprise Manager, or by using the Dbms_scheduler pl/sql package from the command line. 1. Create a program

Use the Create_program procedure to create a program. When you use the scheduler, the creation program is an optional part. An operation can also be encoded so that it executes in an anonymous pl/sql block in the create_job procedure. By creating a program individually, you can define an action and then reuse it in multiple jobs. Using this method, you can change the schedule of a job without recreating the pl/sql block.

By default, programs are created in a disabled state (unless the enabled argument is set to true). The job cannot execute this program until the disabled program is enabled. You can specify that the status creation program should be enabled by specifying the enabled value as true.

2. Create and use scheduling

A job's schedule can be a predefined schedule (created with the Create_schedule procedure), or it can be defined when the job is created.

The schedule specifies the properties for the job run time, for example:

• Start time, define the time at which the job will be executed, end time, specify after which time the job expires, and no longer schedule

• Specify an expression for job recurrence interval

• Complex scheduling created by combining existing schedules

• Conditions or state changes (called events) that must be met before the job is started

You can manage scheduled execution of multiple jobs without updating multiple job definitions by using a schedule instead of specifying the number of times the job is executed in the job definition. If a schedule is modified, each job that uses the schedule automatically uses the new schedule.

3. Create and run jobs

A job is a combination that includes a schedule, a description of the action to be performed, and all additional parameters required by the job. You can set many properties for a job. property controls how the job is executed.

When a job operation is in a program (rather than directly in a job), you can change the job schedule without recreating the Pl/sql block.

Each job must have a schedule. Schedules can be predefined or can be defined during the creation of a job. Durable lightweight job durable lightweight job:

• Reduce the cost and time required to start a job

• Job metadata and run-time data occupy a small amount of space on disk

• Is a BEGIN created using the job template (on the command line)

Dbms_scheduler. Create_job (

Job_name => ' My_lightweight_job2 ',

Program_name => ' My_prog ',

Schedule_name => ' my_sched ',

Job_style => ' lightweight '); End; /

Select the appropriate job type:

– Use regular jobs to provide maximum flexibility.

– Use persistent lightweight jobs when you need to create a large number of jobs in a short amount of time. Lightweight jobs:

• For customers who need to create hundreds of jobs in one second. For regular jobs, each job creates a database object that describes the job, modifies multiple tables, and creates a redo in the process. The associated cost of this type of job requirement is significant. In the Oracle DB Scheduler, there is a "persistent lightweight job." The purpose of the lightweight job is to reduce the cost and time required to start the job. Very little metadata is created for the job. This reduces the time it takes to start the job and the redo that is created.

• Job metadata and run-time data occupy a small amount of space on disk. Small footprint on disk also enables load balancing in a RAC environment.

• Is always created by using job templates. The job template must be a stored procedure or a program. Stored procedures can save all the information required by the job, including permissions. You can specify only a small number of job attributes: Job parameters and schedules.

• Must be created on the command line. The Job_style parameter is not available in EM.

In the example, My_prog is the job template, and the schedule is applied through named schedules. using a time based or event-based schedule

To specify a time-based schedule for a job, you can specify a calendar expression or a date-time expression. When you use a calendar expression, the job's next startup time is calculated using the recurrence interval and the start date of the assignment. When you use a datetime expression, the specified expression determines when the job should run next. If no repeat interval is specified, the job will run only once on the specified start date.

If the job uses an event-based schedule, the job will run when the event occurs. At a higher level, an event can be considered a state change. An event occurs when the state of a Boolean condition changes from false to true, or to false from true.

The scheduler uses an Oracle Streams Advanced Queuing (AQ) to raise and use events.

Note: The scheduler does not guarantee that the job will execute exactly at the scheduled time because the resource may be unavailable due to a system overload. Create a time based job

Example: Create a job to call the backup script every 11:00 from tonight. BEGIN

Dbms_scheduler. Create_job (

Job_name=> ' HR. Do_backup ',

Job_type => ' executable ', job_action =>

'/home/usr/dba/rman/nightly_incr.sh ',

Start_date => Sysdate,

Repeat_interval => ' freq=daily; Byhour=23 ',

/* Next night at 11:00 PM * *

Comments => ' Nightly incremental backups '); End; /

Create a time based job

You can use the Create_job procedure for Dbms_scheduler packages to create jobs. Jobs are created by default in a disabled state, and are only effective when explicitly enabled and can be scheduled. All job names take the following form: [Schema.] Name

You should use Systimestamp and specify the time zone, so that when daylight saving time changes, the job can automatically adjust its execution time.

By default, jobs are created in the current scenario. You can create a job in another scenario by specifying the name of the scenario, as shown in the example. The job owner is the user who created the job in its scenario, and the job creator is the user who created the job. The job executes according to the permissions of the job owner. The national Language Support (NLS) environment for a job run is the same as the environment when the job was created. The Job_type parameter indicates the type of task that the job will perform. Possible values include the following:

Plsql_block: Anonymous pl/sql block

stored_procedure: Named Pl/sql, Java, or external process

executable: Commands that can be executed from the operating system (OS) command line

The job_action parameter can be the name of the procedure to run, the name of the script, or the name of the operating system command, or an anonymous Pl/sql code block, depending on the value of the Job_type parameter.

In the example, Job_type is specified as executable,job_action is the required external executable file plus any command-line arguments (optional) of the operating system-related full path.

An external job is a job that runs outside the database. All external jobs run as low privilege guest users, which are determined when the database administrator configures external job support. Because executables run as a low privilege Guest account, you should ensure that they have access to the necessary files and resources. Most (but not all) platforms support external jobs. For platforms that do not support external jobs, an error is returned if the properties of the job or program are created or set to the executable type. to create an event-based schedule

To create an event-based job, you must set: