9.2 Jobs

There are five options available in the Jobs menu: Summary, Running, Forecast, History, Notifications. Each option is described below.

9.2.1 Summary

At the top, the Summary page provides an overview of the total number of Failed, Blocked, Chain stalled, Resource unavailable, Broken, and Retry Scheduled jobs. Click a job tile and the corresponding list of jobs are displayed in tabular format below. Remove the filters to display all scheduled jobs.

To create a job, see Create or Edit Job.

The Actions icon is available at the end of each job row. Click Actions to view the following list of options:

• Job

  • Run: Runs the specific job.

  • Edit: See Create or Edit Job.

  • Drop: Drops the specific job.

  • Job-Enable/Disable: If enabled is selected, the job is picked up by the Scheduler for processing. The status of the job (enabled or not) is seen in the Job Details page, where the property “enabled” displays TRUE or FALSE.

• History

  • Report: Provides a history of the job runs in a report format, including log details, status of the run, run duration, errors if any, and so on.

  • Chart: Provides a history of the job runs in a visual bar chart representation. Run duration and used CPU time are presented for each execution. Jobs in chains are presented with aggregated information for the whole job. There is an overview scrollbar that enables zoom and scroll by changing the time frame. You can display all details from logs for that time frame for a selected job or for all jobs. Also, a Gantt chart can be shown for all jobs for a selected time frame.

    
    Description of the illustration history_chart_gantt.png

• Job Forecast: You can execute the job forecast functionality for a single job (available in the Action menu) or for a set of jobs (available on the toolbar for the table). For a set of jobs, filtering can be used to narrow the set of jobs. Use Ctrl key+click to select some jobs and then Job Forecast will run for the selected jobs only. If there are no selected jobs, then forecast will run for all listed jobs. Not every job included in the set is included in the final forecast. Only jobs with a defined calendar (repeat interval) are included. You can define the calendar inline or using schedule, window or window group. After the forecast is done, you can select different zoom levels and filter the results by schema.

• Job Details: Displays job attributes, such as action, job class, type, schedule, and so on. Select JSON to view the attributes in JSON format. Depending on the job’s definition, details about used objects are provided such as used program, PL/SQL code, procedure, procedure dependencies, program arguments, job arguments, job class, schedule, window, window group together with windows in the group, file watcher, history chart presenting one week of history from last start date.

9.2.2 Running

The Running page displays the list of currently running jobs. The following commands are available: Stop, Edit, History, Job Details.

9.2.3 History

The History page displays log run details for all Scheduler jobs available to the user. You can use the History window and set filters to limit the amount of data. You can filter using delayed jobs by providing a delay interval. Ordering is supported on the grid by clicking the column header.

9.2.4 Forecast

The Forecast page provides the job execution forecast for all available jobs. This operation takes time to finish depending on the number of jobs and forecast interval. Forecast for all available jobs depends on the rights of the connecting user. After execution, you can filter the results based on the schema of the jobs.

The Forecase functionality is also available on the Jobs - Summary page. In that case, it works on the list of available jobs. You can filter the jobs before the forecast functionality is used. If there is a selection of jobs, then the functionality uses only the selected jobs.

Description of the illustration forecast.png

9.2.5 History (Gantt Chart)

History represents windows and job execution history in the form of a Gantt chart. The windows activation history is shown on the first row. Jobs are ordered in a descending order based on the maximum used CPU time. Details are shown for each window activation and job execution. The job summary is available when hovering over the label of a job's row.

9.2.6 Notifications

You can create notifications only if an email server is set for the scheduler.

The Notifications page enables you to view, create, edit and delete notifications related to job events. Also, you can see email server details by clicking the Notifications Email Server icon at the top right of the page.

For each message, you can specify job, recipient email addresses and sender (or no sender), and you can modify the subject and body of the message and set the filter condition. If multiple recipients and events are provided, then Oracle Scheduler creates a separate notification record for each combination <recipient,event>. You can edit the content of a set of notifications created in this way by using Edit Aggregated, which appears in the context menu of each notification. Edit edits the content of a single notification.

Notifications can be filtered by job name, job owners, recipients and events. You can select and remove some notifications by clicking the Remove Notifications icon. If there are no selected notifications (use Ctrl+click to deselect a single notification), then the Remove Notifications dialog appears enabling removal of all notifications for the selected job, or job and recipients, or job and events, or job and events and recipients.

Description of the illustration remove_notifications.png

9.2.7 Create or Edit Job

This section describes how to create a new Oracle Scheduler job or edit an existing job.

To create a job, Database Actions internally uses the DBMS_SCHEDULER.CREATE_JOB procedure, which is documented in Oracle Database PL/SQL Packages and Types Reference.

1. In the Jobs page, at the top right, click Create Job.
2. In Job Properties, enter the following fields:

   Details

   • Enabled: If this option is specified, validity checks are made and the job is created enabled if all the checks are successful. If this option is not specified, the job is not created enabled.
   • Name: Name of the job.
   • Description: Optional text string that can be used to describe the job.

   • Type: Type of object to be executed by the job: PL/SQL Block, Chain, Stored Procedure, Named Program, or Script. Additional controls appear for Chain, Stored Procedure and Named Program enabling you to select related objects.

     For Stored Procedure, only procedures with IN parameters are listed, procedures with IN OUT or OUT parameters are not permitted.

     Schema level and package level procedures are listed for the selected schema but you can directly type the procedure name (or package_name.procedure_name) in the field.

   • Class: Name of the job class to which this job belongs.

   Execution Mode

   • Mode: When to execute the job: Immediate (immediately on creation, and once only), Once (once, at a specified time), Repeating, Queue, File Watcher, Schedule (using a named schedule object), Window and Window group. If you specify anything other than Immediate, you are prompted for additional information.

     For Repeating, you can manually define the repeat interval or click the pencil icon to select the date, frequency, weekday, and interval values. Some clauses of Oracle calendar syntax (include, exclude, intersect, periods and by period) are not supported and a warning is displayed when the edit icon is clicked.

   Destination

   • Local (local system), Remote (the database destination for a remote database job, or external destination for a remote external job), or Multiple (the job runs on all destinations associated with the provided destination group). Depending on what destination you selected for the job, select the local credential, the remote credential and destination, or the destination group.

   Properties

   • Auto Drop: Determines whether the job is to be automatically dropped after it has completed or has been automatically disabled.

   • Restart on failure: Determines whether the job can be restarted in case of failure.

   • Restart on recovery: Determines whether to restart the job in case of database failure.

   • Store Output: If enabled, then for job runs that are logged, all job output and error messages are stored in the *_JOB_RUN_DETAILS views. If disabled, then the output and messages are not stored.

   • Follow Default Time Zone: Determines whether if the job start date is null, then when the default time zone scheduler attribute is changed, the Scheduler recomputes the next run date and time for this job so that it is in accordance with the new time zone.

   • Allow Runs in Restricted Mode: If enabled, the job is permitted to run when the database is in restricted mode, provided that the job owner is permitted to log in during this mode.

   • Stop on Window Close: If the schedule of a job is a window or a window group, enabling this option causes the job to stop once the associated window is closed, and disabling causes the job to continue after the window closes. (Note that if the job is allowed to continue, its resource allocation will probably change because closing a window generally also implies a change in resource plans.)

   • Instance Stickiness: This attribute should only be used for a database running in an Oracle Real Application Clusters (Oracle RAC) environment. By default, it is enabled. Jobs start running on the instance with the lightest load and the Scheduler thereafter attempts to run on the instance that it last ran on. If that instance is either down or so overloaded that it does not start new jobs for a significant period of time, another instance runs the job. If the interval between runs is large, instance_stickiness is ignored and the job is handled as if it were a non-sticky job. If instance_stickiness is disabled, each instance of the job runs on the first instance available.

   • Parallel Instances: For an event-based job, determines what happens if an event is raised and the event-based job that processes that event is already running. If disabled, it causes the new event to be ignored. If enabled, it causes an instance of the job to be started for every instance of the event, and each job instance is a lightweight job so multiple instances of the same event-based job can run in parallel.

   • Job Style: Style of the job being created: REGULAR (regular job) or LIGHTWEIGHT (lightweight job). A lightweight must reference a program object. Use lightweight jobs when you have many short-duration jobs that run frequently. Under certain circumstances, using lightweight jobs can deliver a small performance gain.

   • Job Priority: The priority of this job relative to other jobs in the same class as this job. If multiple jobs within a class are scheduled to be executed at the same time, the job priority determines the order in which jobs from that class are picked up for execution by the job coordinator. It can be a value from 1 through 5, with 1 being the first to be picked up for job execution.

   • Logging Level: Determines how much information is logged: DBMS_SCHEDULER.LOGGING_OFF (no logging), DBMS_SCHEDULER.LOGGING_FAILED_RUNS (only jobs that failed, with the reason for failure), DBMS_SCHEDULER.LOGGING_RUNS (all runs of each job in this class), or DBMS_SCHEDULER.LOGGING_FULL (all operations performed on all jobs).

     However, if the job class has a higher (more detailed) logging level than the level specified for the job, the job class logging level is used.

   • Instance ID: In an Oracle Real Application Clusters environment., the instance ID of the instance that the job must run on.

   • Max Runs: The maximum number of consecutive scheduled runs of the job.

   • Max Failures: The number of times a job can fail on consecutive scheduled runs before it is automatically disabled.

   • Raise Events: Determines at what stages of the job execution to raise events.

   • Max Run Duration: Maximum amount of time that the job should be allowed to run. Its data type is INTERVAL DAY TO SECOND. If this attribute is set to a nonzero and non-null value, and job duration exceeds this value, the Scheduler raises an event of type JOB_OVER_MAX_DUR. It is then up to your event handler to decide whether or not to allow the job to continue.

   • Schedule Limit: Maximum delay time between scheduled and actual job start before a program run is canceled.

   • Reset to Defaults: Resets all properties to their default values.

   NLS

   Enables you to set the NLS-related property required for this job. To enter a value, type in to the related field for the Value column.

   For a new job, parameters are taken from the database session.

3. In the DDL pane, you can review and save the SQL statements that are generated when creating or editing the job.

   • For a new job, click CREATE to view the generated DDL statements.

   • When you edit a job, click UPDATE to view the generated ALTER statements.

   When you are finished, click Apply.

   The Output pane displays the results of the DDL commands. If there are any errors, go to the corresponding pane, fix the errors, and run the commands again. You can save the content to a file.