Oracle Scheduler (Scheduler) excerpted from a great God

In 11g, Oracle provides a new scheduler feature that helps automate job implementation. It can also help you control the utilization of resources and can execute jobs in the database in order of precedence. One limitation of traditional dbms_jobs is that it can only dispatch PL/SQL-based jobs and cannot be used to dispatch executable files or scripts for the operating system.

The scheduler includes the following permissions: The Scheduler_admin role contains all scheduler system permissions and is authorized as follows:sql> Grant Scheduler_admin to djp01  2  /  grant Succeeded. sql>manage Scheduler system permissions allow you to do the following: ' Create, delete, and change job classes, windows and window groups. Stop the job early start and stop windows (about these parts are described below). Authorized as follows:sql> Grant Manage scheduler to djp01  2  / grant succeeded. sql>create Job system permissions allow you to do the following: Create job (Job), Progress meter (schedule), program, Chain (chain), and events (event). (for these parts are described below), authorized as follows:sql> Grant create job to djp01  2  / grant succeeded. sql> If the program to be executed is in a different mode, then it is necessary to have execute permission to execute the program in the corresponding mode, as follows execute any procedure.  scheduler includes the following basic components: (1) The job job is a task that is scheduled to perform one or more times. Examples are as follows:sql> alter session set Nls_date_format = ' Yyyy-mm-dd hh24:mi:ss '   2  / session altered. sql& Gt Alter session Set Nls_timestamp_tz_format = ' Yyyy-mm-dd hh24:mi:ss.ff '   2  / session altered. sql > Alter session Set Nls_timestamp_format = ' Yyyy-mm-dd hh24:mi:ss.ff '   2  / session altered. sql& Gt

Description: When using the scheduler, there will be some time related information, I am here to make some time format settings for ease of use.

Sql> CREATE TABLE emp_bak  2  as  3  select *  4  from employees  5  where 1 = 0  6  / table created. sql>  sql> begin  2     DBMS_ Scheduler.create_job (  3        job_name=> ' Test_job ',  4        job _type=> ' Plsql_block ',  5        job_action=> ' INSERT INTO Emp_bak SELECT * FROM Employees WH ere rownum <=, ',  6        start_date=> ' 2012-12-09 14:40:00 ',  7     &NB Sp  repeat_interval=> ' freq=daily;interval=2 ',  8        end_date=> ' 2012-12-09 20:00:00 ',  9        comments=> ' insert old employees into the Emp_bak table ',  10   & nbsp    auto_drop=>false, 11        enabled=>true);  12  end; 13  / pl/sql procedure successfully completed. SQL> Description: job_name Specifies the name of the job; job_type Specifies the type of job to create, whose values include plsql_block (PL/SQL block), stored_procedure (stored procedure), Executable (executable file or Java program). job_action specifies the job to execute procedures, commands, or scripts. Start_date and End_date Specify the start and end time of the job. Comments is used to add comments to the current job. Enabled Specifies whether the job is started or disabled when the job is created, the default value is False, the value is true, and the table is turned on. AUTO_DROP Specifies whether the job will be deleted automatically after execution completes, and the default value is true, which means that the job is automatically deleted after execution completes. REPEAT_INTERVAL Specifies how often the job executes, freq=daily;interval=2 indicates that the job runs once a day. The value is a calendar expression (calendaring expression) consisting of three parts, Frequency: This is the part that the expression must contain, specified with Freq, and may take a value of yearly,monthly,weekly,daily, Hourly,minutely and secondly. Repeat interval: This interval is identified by the interval keyword, which represents the frequency of execution. Specific: Provides details about when a job will run, possibly with values of: Bymonth,byweekno,byyearday,bymonthday,byday,byhour,byminute and Bysecond. For example: ByMonthDay represents a day of the month, and Byday represents a day of the week. The following is a typical calendar expression: freq=daily;interval=3; executes once every three days. freq=hourly;interval=2; execute once every hour. Freq=weekly,byday=sun; executed once every Sunday. Freq=weekly;interval=2,byday=fri; executed once every other week in Friday. Freq=monthly;bymonthday=1; executes once on the last day of the month. freq=minutely;interval=30; run every half an hour. Let's take a look at what's happening:sql> Select COUNT (*)   2  from emp_bak  3  /   CounT (*)----------       200 SQL> The above scheduler's job runs successfully, let's look at some information about the job: sql> select Job_style ,job_type,job_action,program_name,state  2  from dba_scheduler_jobs  3  where job_name = Upper (' Test_job ')   4  / job_style              job_ TYPE------------------------------------------------------job_ ACTION--------------------------------------------------------------------------------PROGRAM_ NAME--------------------------------------------------------------------------------State------------------------------RE Gular                plsql_blockinsert into Emp_bak select * FROM Employees where RowNum <= 200; completed  sql> Status shows that the job execution is complete and the job type is regular (regular). Some other management of   jobs: Disable a job:sql> exec dbms_scheduler.disable (' test_job ');  pl/sql procedure successfully Completed. sql> activating a job:sql> exec dbms_scheduler.enable (' test_job '); PL/SQL procedure successfully completed. sql> runs a job:sql> truncate TABLE emp_bak; table truncated.  SQL> exec dbms_scheduler.run_job (' test_job ');  pl/sql procedure successfully completed. sql> Select COUNT (*)   2  from emp_bak  3  /   count (*)----------       200& nbsp Sql> Description: This run is equivalent to manually executing the corresponding program. Stop a job:sql> exec dbms_scheduler.stop_job (' test_job '); If a job is not running, it will have a ORA-27366 error. Delete a job:sql> exec dbms_scheduler.drop_job (' test_job ');  pl/sql procedure successfully completed. sql>  

(2) Progress meter (schedule)

A progress meter is a description of the time and frequency at which the database executes a job. If some of the jobs are running at roughly the same time, then a schedule can be used to create and manage these jobs. Examples are:sql> begin  2     Dbms_scheduler.create_schedule (  3        schedule_ Name=> ' Test_schedule ',  4        start_date=>systimestamp,  5       Nbsp;repeat_interval=> ' freq=minutely;interval=30 ',  6        end_date=>systimestamp+ 3,  7        comments=> ' every Minute ');  8  end;  9  / pl/sql proc Edure successfully completed. sql> below we view the creation information for this schedule:sql> select start_date,end_date,schedule_type  2  from dba_scheduler_schedules  3  where schedule_name = Upper (' test_schedule ')   4  /  Start_date---------------------------------------------------------------End_ DATE---------------------------------------------------------------Schedule_type----------------2012-12-09 17:22:43.7810002012-12-12 17:22:43.000000calendar sql>

The

can make changes to the related properties of the progress table that you have already created, as follows:

Sql> begin  2     Dbms_scheduler.set_attribute (  3        name=> ' Test_ Schedule ',  4        attribute=> ' start_date ',  5        value=> Systimestamp);  6  end;  7  / pl/sql procedure successfully completed. sql> on the existing schedule, We can delete the following:sql> exec dbms_scheduler.drop_schedule (' test_schedule ');  pl/sql procedure successfully The completed. sql>  (3) program includes metadata about a scheduler job. Programs include program names, program types, and program actions. It is the actual name of a procedure or executable script. Examples are:sql> begin  2     Dbms_scheduler.create_program (  3        program_name= > ' Test_program ',  4        program_type=> ' stored_procedure ',  5        program_action=> ' auto_archive_emp ',  6        enabled=>true,  7        comments=> ' use to execute the procedure of Auto_archive_emP ');  8  end;  9  / pl/sql procedure successfully completed. sql> description: PROGRAM_ The type and program_action are the same as the job_type,job_action meanings above, which are no longer explained here. Enabled indicates whether the program is enabled, and the default value is False, which means disable. We can enable or disable a program in the following ways: Disable a program:sql> exec dbms_scheduler.disable (' Test_program ');  pl/sql procedure Successfully completed. sql> enable a program:sql> exec dbms_scheduler.enable (' Test_program ');  PL/SQL Procedure successfully completed. sql> Similarly, we can also delete the following:sql> exec dbms_scheduler.drop_program (' Test_ Program ');  pl/sql procedure successfully completed. sql> viewing information about programs can use the Dba_scheduler_programs data dictionary.

 

(4) chains (chain) can use the concept of a scheduler chain to link related programs together. Therefore, the successful operation of a program may be based on the successful operation of another program, or it can be based on a key rather than a program startup job. A continuous position in a chain is called a "step" of the chain, and each step points to another chain, program, or event. Because the chain uses the Oracle Streams rules engine (Oracle Flow rule engines object). Therefore, the user must have create job and rules engine permissions in order for a chain to be created. Examples are as follows: Authorization:sql> begin  2     Dbms_rule_adm.grant_system_privilege (  3        dbms_rule_adm.create_rule_obj, ' Djp01 ');  4     Dbms_rule_adm.grant_system_privilege (  5        dbms_rule_adm.create_rule_set_obj, ' djp01 ');  6     Dbms_rule_adm.grant_ System_privilege (  7        dbms_rule_adm.create_evaluation_context_obj, ' djp01 ');  8  end;  9  / pl/sql procedure successfully completed. sql> created as follows:sql> begin  2     Dbms_scheduler.create_chain (  3        chain_name=> ' Test_chain ',  4        comments=> ' A shain test ');  5  end;  6  / pl/sqlProcedure successfully completed. sql> define chain steps:sql> begin  2     Dbms_scheduler.define_chain_ Step (  3        chain_name=> ' Test_chain ',  4        step_name=> ' Test_step1 ',  5        program_name=> ' test_program1 ');  6     DBMS_ Scheduler.define_chain_step (  7        chain_name=> ' Test_chain ',  8        step_name=> ' TEST_STEP2 ',  9        program_name=> ' test_program2 ');   end; 11  / pl/sql Procedure successfully completed. sql> description: The chain step can point to a program, chain, or event. Use a different parameter for identification. To use chain-efficient runs, you must add a rule to the chain that determines when the step runs and specifies the run condition. Below:sql> begin  2     Dbms_scheduler.define_chain_rule (  3        chain_name=& gt; ' Test_chain ',  4        condition=> ' true ',  5        action=> ' Start Test_step1 ');   6     Dbms_scheduler.define_chain_rule (  7        chain_name=> ' Test_chain ',   8        condition=> ' Test_step1 completed ',  9        action=> ' Start Test_step2 ');  10     Dbms_scheduler.define_chain_rule ( 11        chain_ Name=> ' Test_chain ',  12        condition=> ' Test_step2 completed ',  13        action=> ' end ');  14  end; 15  / pl/sql procedure successfully completed.  SQL> Description: In the rules, we define each step in order. If each step has a transactional relationship, using the chain is a good choice.

After the above steps have been completed, we also need to enable the chain, as follows:

sql> exec dbms_scheduler.enable (' Test_chain '); PL/SQL procedure successfully completed. Sql> at this point, we can use the chain in the Job (job) (Job_type specifies the name of the corresponding chain for chain,job_action, such as Test_chain). We can also run:sql> begin 2 Dbms_scheduler.run_chain (3 chain_name=> ' Test_chain ', 4 start_steps= using the following method  > ' Test_step1,test_step2 ');  5 end; 6/pl/sql procedure successfully completed. Sql> (5) Events (event)

Scheduler triggers events and initiates event-based database jobs using the Oracle Stream Advanced qeueing (Oracle streaming premium queue). An event is a message sent by an application or process that takes note of an event or action. There are two types of events: the events raised by scheduler and the events raised by the application. Scheduler events are caused by changes in the scheduler run, such as the successful completion of a scheduler job is an event. An application-raised event is used or consumed by scheduler to start a job.

Use event-based jobs, as long as you specify Event_condition and Queue_spec on the create_job process. But the definition of the event involves some use of the Dbms_aqadm package. I looked at the DBMS_AQADM package statement, there is no relevant comments, and then tried several times, did not achieve the effect, the problem remains to be resolved.   Let's take a look at a few examples of integrated use:sql> begin  2     Dbms_scheduler.create_job (  3         Job_name=> ' Test_job1 ',  4        program_name=> ' test_program1 ',  5     & nbsp  schedule_name=> ' Test_schedule ',  6        enabled=>true,  7     & nbsp  auto_drop=>false,  8        comments=> ' use program and schedule to create job '); &nbs P 9  end; 10  / pl/sql Procedure successfully completed. sql> description: In the creation of the above job, I used the program and the Progress table. If you want to make adjustments, you can adjust the corresponding program and the degree. Sql> begin  2     Dbms_scheduler.create_job (  3        job_name=> ' Test_ Job2 ',  4        schedule_name=> ' Test_schedule ',  5        job_typE=> ' chain ',  6        job_action=> ' Test_chain ',  7         enabled=>true,  8        auto_drop=>false,  9        comments=& gt; ' Use chain to create job ');  10  end; 11  / pl/sql procedure successfully completed. sql > Description: When creating a job, we can also specify a chain of two. If you want to specify a chain, then Job_type is chain,job_action for the chain name created.

Oracle Scheduler (Scheduler) excerpted from a great god