Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what functions can Oracle Scheduler achieve". In daily operation, I believe many people have doubts about what functions Oracle Scheduler can achieve. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful for you to answer the doubts about "what functions can Oracle Scheduler achieve?" Next, please follow the editor to study!

Oracle Scheduler can help DBA or database users automatically schedule and run various types of jobs, such as database backup, collect optimizer statistics, generate various reports or execute business processes, etc., or combine Schedule and Resource Manager to activate a specified Resource Plan through a time window to complete a variety of complex requirements for enterprise resource management and job scheduling. Similar functionality can be achieved through the DBMS_JOB tool before 10g, but there is no doubt that DBMS_SCHEDULER is much more flexible and diverse than the older version of the tool.

First, functional overview

What specific functions can Oracle Scheduler achieve? Let's take a look at the explanation given in the official documentation:

. Run database program units

Database programs that can be executed in local or remote databases include: PL/SQL anonymous blocks, PL/SQL stored procedures, JAVA stored procedures, and chains

. Run external executables, (executables that are external to the database)

Execute external executables, including applications, Shell scripts, Windows batch files, and if you want to execute external jobs on a remote host, the remote host only needs to install Agent and does not need Database

. Schedule job execution using the following methods:

. Time-based scheduling

You can schedule a job to run at a particular date and time, either once or on a repeating basis.

. Event-based scheduling

You can start jobs in response to system or business events. Your applications can detect events and then signal the Scheduler. Depending on the type of signal sent, the Scheduler starts a specific job.

.Dependency scheduling

You can set the Scheduler to run tasks based on the outcome of one or more previous tasks. You can define complex dependency chains that include branching and nested chains.

. Prioritize jobs based on business requirements.

.Controlling Resources by Job Class

Assign the Job with the same characteristics to a Job Class, and then map the Job Class to Resource Consumer Group to realize the resource allocation control of the scheduling job.

.Controlling Job Prioritization based on Schedules

Change the priority of the job through the time window, create a Window to activate the corresponding Resource Plan in a time range to control the priority of different jobs in different periods of time.

. Manage and monitor jobs

. Execute and manage jobs in a clustered environment

Support to manage and schedule jobs in RAC environment

Second, basic concepts

The main objects that Oracle Scheduler contains include: Schedule,Program,Job,Job Class,Chain,Window,Database Destination,File Watcher,Credential... The details are as follows:

. Schedule (schedule)

Define the start time, end time, and repeat interval of the schedule through the process CREATE_SCHEDULE in the DBMS_SCHEDULER package

The CREATE_EVENT_SCHEDULE process is used to create a timesheet triggered by an event, and a task is called up by an event within a specific time period.

. Program (program)

The form and content of the job are defined. The job form can be an PL/SQL anonymous block, a stored procedure or an external executable. The executed stored procedure must be run as an anonymous block with input parameters.

. Job (Job)

Create a job through CREATE_JOB. The stored procedure uses overloading, so the choice of input parameters can be very flexible. You can not only set the time, content and Job Class of the job independently, but also refer to the existing Schedule and Program to simplify the creation of the job.

. Job Class (job class)

Resource Consumer Group, Service (RAC), log level, log retention time are defined in the job class. It is important to note that in Resource Manager, the service parameter maps the session to a resource user group. If both the RAC node and the user group are specified, the user group parameter takes precedence.

. Chain (chain)

To put it bluntly, a series of assignments build a set of dependency relationships through chains. The general idea is as follows: first, make CREATE_CHAIN to create a chain, then define an alias (step_name) for the Program, Sub Chain, Event Condition and Event Schedule to be linked through DEFINE_CHAIN_STEP and DEFINE_CHAIN_EVENT_STEP, and then define these Step dependency rules with DEFINE_CHAIN_RULE. After judging, branching and nesting, the chain is created. In CREATE_JOB (job_type = > 'CHAIN',job_action = >' chain_name'), you have to pay attention to being a person. This must be pictured above:

. Window (window)

Window refers to a period of time between the start time and the end time. (why is such a simple concept so eloquent when I say it? what about my primary school Chinese teacher? Do you have anything to say.) It is usually used to call up jobs, or activate different Resource Plan in different time periods to adjust the resource allocation between jobs. Overlapping Windows is also mentioned in the document, so we will not study it for the time being. Come on, there is a picture and the truth:

. Database Destination (database path)

Created by CREATE_DATABASE_DESTINATION and used as an input parameter in CREATE_JOB to perform remote scheduling

. Credential (Certificate)

The certificate is used to hold the user password for OS or database, which is used when executing External Job or remote database jobs.

. File Watcher (File Monitor)

This is fun. It can be used to detect the existence of an OS file, determine whether the transfer is completed according to the file size, and then trigger an Event Schedule

. Lightweight Job (lightweight homework, why is my color wrong?)

11gR1 was not supported before. As specified by job_style, a lightweight job is not a scheduler object. It is suitable for multiple small jobs that are executed frequently. See the official documentation:

Unlike regular jobs, they are not schema objects.

They have significantly better create and drop times over regular jobs because they do not have the overhead of creating a schema object.

They have lower average session create time than regular jobs.

They have a small footprint on disk for job metadata and run-time data.

Third, the creation process

-- official grammar:

1. Create a scheduling job and execute a Shell script

Executing the Shell script generates a file at / tmp:

[oracle@ASM ~] $cat / tmp/job.sh

#! / bin/bash

/ bin/touch / tmp/abc.txt

Create a certificate, create and execute the job immediately:

SQL > exec dbms_scheduler.create_credential ('CRE1','user1','abcd1234')

PL/SQL procedure successfully completed.

SQL > exec dbms_scheduler.create_job (dbms_scheduler.generate_job_name,'EXECUTABLE','/tmp/job.sh',0,'','DEFAULT_JOB_CLASS',TRUE,FALSE,'Test.','CRE1')

PL/SQL procedure successfully completed.

SQL > select job_name,state,run_count,failure_count,raise_events from dba_scheduler_jobs where comments='Test.'

JOB_NAME STATE RUN_COUNT FAILURE_COUNT RAISE_EVENTS

--

JOB$_127 SUCCEEDED 1 0

SQL > select JOB_NAME,STATUS,CREDENTIAL_NAME,ADDITIONAL_INFO from dba_scheduler_job_run_details where job_name='JOB$_127'

JOB_NAME STATUS CREDENTIAL ADDITIONAL_INFO

-

JOB$_127 SUCCEEDED CRE1 EXTERNAL_LOG_ID= "job_74659_457"

USERNAME= "user1"

Check whether the job was executed successfully

[oracle@ASM ~] $ls / tmp/abc.txt

/ tmp/abc.txt

2, execute SQL statement, anonymous block and PL/SQL stored procedure respectively

SQL > exec dbms_scheduler.create_job (dbms_scheduler.generate_job_name,'PLSQL_BLOCK','insert into scott.t1 (dname) values (''BLOCK'');', 0pr.

PL/SQL procedure successfully completed.

SQL > exec dbms_scheduler.create_job (dbms_scheduler.generate_job_name,'PLSQL_BLOCK','BEGIN p2)

PL/SQL procedure successfully completed.

SQL > exec dbms_scheduler.create_job (dbms_scheduler.generate_job_name,'STORED_PROCEDURE','p1',0,'','DEFAULT_JOB_CLASS',TRUE,FALSE,'Test_2')

PL/SQL procedure successfully completed.

3, create a Program and reference it in Job

SQL > exec dbms_scheduler.create_program ('TEST1','PLSQL_BLOCK','BEGIN p2'); END;',0,TRUE,'Pro_1')

PL/SQL procedure successfully completed.

SQL > exec dbms_scheduler.create_job (dbms_scheduler.generate_job_name,program_name = > 'TEST1',repeat_interval = >', end_date = >'', enabled = > TRUE, auto_drop = > FALSE,comments = > 'Test_9')

PL/SQL procedure successfully completed.

4. Reference Program and Schedule when creating Job

SQL > BEGIN

2 DBMS_SCHEDULER.CREATE_SCHEDULE (

3 SCHEDULE_NAME = > 'sch2'

4 START_DATE = > sysdate

5 END_DATE = > sysdate+3650

6 REPEAT_INTERVAL = > 'freq=yearly; bymonth=jan,apr,jul,oct; bymonthday=2',-jobs are executed on the second day of the first month of each quarter for 10 years

7 COMMENTS = > 'Quarterly Report.'

8)

9 END

10 /

PL/SQL procedure successfully completed.

SQL > BEGIN

2 DBMS_SCHEDULER.CREATE_JOB (

3 dbms_scheduler.generate_job_name

4 PROGRAM_NAME= > 'TEST1'

5 SCHEDULE_NAME= > 'sch2'

6 ENABLED= > TRUE

7 AUTO_DROP= > FALSE

8 COMMENTS= > 'Test_99'

9)

10 END

11 /

PL/SQL procedure successfully completed.

4. Dbms_scheduler package some views

-- * stands for all or dba or user

-- 5.1. View the view of job

Dba_scheduler_jobs-View job

Dba_scheduler_job_args-View all input parameters of job

* _ scheduler_job_classes-- View the class information of job

* _ scheduler_job_dests-- View job status

* _ scheduler_job_log-- View job log

* _ scheduler_job_run_details-- View the details of job execution

* _ scheduler_running_jobs

-- 5.2. View some views of chain

* _ scheduler_chains

* _ scheduler_chain_rules

* _ scheduler_chain_steps

* _ scheduler_running_chains-View the chains that is being executed

-- 5.3. View the view of program

* _ scheduler_programs-View the program

* _ scheduler_program_args-- View program parameters

-- 5.4. View scheduling group scheduler_group information

* _ scheduler_groups

* _ scheduler_group_members

-- 5.5. View a view of window (this type of view only starts with dba and all)

* _ scheduler_windows-- View window

* _ scheduler_window_details-- View window details

* _ scheduler_window_groups-- View window groups

* _ scheduler_window_log-- View window log

* _ scheduler_wingroup_members-- View window members

-- 5.6. View the scheduler view

* _ scheduler_schedules-- View scheduling

* _ scheduler_global_attribute-displays all scheduling properties

* _ scheduler_credentials

* _ scheduler_db_dests

* _ scheduler_dests

* _ scheduler_external_dests

* _ scheduler_file_watchers

* _ scheduler_notifications

* _ scheduler_remote_databases

* _ scheduler_remote_jobstate

At this point, the study of "what functions can be achieved by Oracle Scheduler" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.