Oracle Scheduling (Schedule) detailed (turn)

Part 1

1. Create Job

What job is it? Simply put, the plan (schedule) plus the task description. In addition, there are some necessary parameters.

The "Tasks" mentioned here can be stored procedures within a database, anonymous pl/sql blocks, or operating system-level scripts.

There are two ways to define "plan":
1) using Dbms_schduler. Create_schedule defines a plan;
2) Call Dbms_schduler. The Create_jobe procedure is specified directly (described below)

When you create a plan, you need to specify at least the following attributes, which are required for the job to run:

Start time (start_time);

Repetition frequency (repeat_interval);

End Time (end_time)


In addition, there are many additional parameters for a job:

Job_class
Job_priority
Auto_drop
Restartable
Max_runs
Max_failures
Schedule_limit
Logging_level


Below, I explain in the form of question and answer.


Q1: How do I query the properties of a job from a database?

A1: There are two ways:

1) Query (dba| all| USER) _scheduler_jobs View
(Hint: Depending on the user's rights, the selective query dba| all| User view)

2) Call the Get_attribute process in the Dbms_scheduler package


Q2: How do you set these properties?

A2: There are two ways

1 Specify directly when creating a job

2) Call the Set_attribute process in the Dbms_scheduler package


Q3: "What permissions do I need to create a job?"

A3: You need to at least create_job this system permission. If the user has the Create any job this permission,
It can create a job that is the owner of any user except the SYS user.

By default, the job is created under the current schema and is not active; If you want the job one to create
To automatically activate, you need to explicitly set the Enabled property to True to see an example:

Begin
Dbms_scheduler.create_job
(
Job_name => ' Arc_move ',
Schedule_name => ' Every_60_mins ',
Job_type => ' executable ',
Job_action => '/home/dbtools/move_arcs.sh ',
Enabled => True,
Comments => ' move archived Logs to a differentdirectory '
);
End
/


Q4: Can you tell me more about the parameters used in the above process?

A4:
Job_name: By definition, each job must have a name
Schedule_name: If a plan is defined, specify the name of the plan here
Job_type: There are currently three types of support:

Pl/sql Block: Plsql_block,
Stored procedures: stored_procedure
External program: Executable (external program can be a shell script, or it can be an operating system level instruction).

Job_action: According to Job_type's different, job_action has different meanings.
If the job_type specifies a stored procedure, the name of the stored procedure needs to be specified;
If the Job_type specifies a pl/sql block, the complete pl/sql code needs to be entered;
If you job_type the specified external program, you need to enter the name of the script or the instruction name of the operating system

Enabled: As mentioned above, specify whether job creation is automatically activated
Comments: A simple description of the job



2. Specify how often the job is performed

If we create a job and want it to run according to the date and time we specify, we need to define
The job is repeated frequently. For example run every Sunday 22:00 run every Monday, three, five run each year
Last Sunday to run and so on.
(Note: 10G previous version, with the operating system, the interaction, not achieve very good.) For example, to implement a
The regular Rman backup task needs to be implemented in conjunction with OS commands, which can be implemented with crontab under UNIX,
Implemented under Windows with the AT command)

10G has been greatly enhanced in this way because the job can be created directly by specifying the operating system commands or
Script, and then reasonably define the execution frequency of the job, you can easily complete the complex scheduling task.

10G supports two modes of repeat_interval, one is pl/sql expression, which is also dbms_job packet
Used in, such as sysdate+1, Sysdate + 30/24*60; The other is a calendar expression.
For example, Mon says Monday, Sun says Sunday, Day says daily, week says weekly and so on. Down here.
Look at several examples of using calendar expressions:

Repeat_interval => ' freq=hourly; Interval=2 '
Run job once every 2 hours

Repeat_interval => ' freq=daily '
Run a job once a day

Repeat_interval => ' freq=weekly; Byday=mon,wed,fri "
Weekly 1,3,5 Run Job

Repeat_interval => ' freq=yearly; Bymonth=mar,jun,sep,dec; Bymonthday=30 '
The 3,6,9,12 month of the year, run job number 30th.

People who have used crontab should have a sense of déjà vu, hehe


Let's say the rules for using calendar expressions:

The calendar expression basically divides into three parts: the first part is the frequency, namely "freq" this keyword,
It is to be specified; The second part is the time interval, which is the keyword "INTERVAL",
The value range is 1-999. It is an optional parameter; The last part is an additional parameter that can be used to
Specifying a date and time accurately is also an optional parameter, such as the following values are valid:
Bymonth,byweekno,byyearday,bymonthday,byday
Byhour,byminute,bysecond

For detailed parameter description, please refer to Dbms_scheduler's instructions for use.

Now that you're talking about repeat_interval, you might ask, "Is there an easy way to draw,
Or is it to evaluate each run time of the job and the next run time?

The Dbms_scheduler package provides a process evaluate_calendar_string that can be very
Easily complete this requirement. Look at the following example:

Sql> set serveroutput on size 999999

Sql> Declare
L_start_date TIMESTAMP;
L_next_date TIMESTAMP;
L_return_date TIMESTAMP;

Begin
L_start_date: = Trunc (Systimestamp);
L_return_date: = l_start_date;
For Ctr in 1..10 Loop
Dbms_scheduler.evaluate_calendar_string (
' Freq=daily; Byday=mon,tue,wed,thu,fri; Byhour=7,15 ',
L_start_date, L_return_date, l_next_date
);

Dbms_output.put_line (' Next Run on: ' | |
To_char (l_next_date, ' mm/dd/yyyy hh24:mi:ss ')
);

L_return_date: = l_next_date;

End Loop;
End
/


The output results are as follows:

Next Run on:03/22/2004 07:00:00
Next Run on:03/22/2004 15:00:00
Next Run on:03/23/2004 07:00:00
Next Run on:03/23/2004 15:00:00
Next Run on:03/24/2004 07:00:00
Next Run on:03/24/2004 15:00:00
Next Run on:03/25/2004 07:00:00
Next Run on:03/25/2004 15:00:00
Next Run on:03/26/2004 07:00:00
Next Run on:03/26/2004 15:00:00


In the next section, I'll summarize how to create and manage with Dbms_scheduler packets
Programs (program), Planning (Schedule).

Part2

3. Creating Programs (Program)

What is a program? My understanding is to prepare the required metadata for the plan (metadata), which
The following sections are included:

Program name;

Parameters used in the program: such as the type of program, and the description of the specific operation

Take a look at an example
Begin
Dbms_scheduler.create_program (
Program_name=> ' Daily_backup_sh ',
Program_type=> ' executable ',
Program_action=> '/home/oracle/script/daily_backup.sh ');
End
/

This example will create a program called "Daily_backup_sh", which is the executable shell script, and the script name is "/home/oracle/script/daily_backup.sh"


Q1: What's the difference between a program and a job?

A1: The program can actually be separated from the job, so different users can reuse it at different time periods. and a job belongs to a specific user;

In addition, separating the program from the job activates a new library (programlibrary), which allows users to freely choose a particular program to run at a specific time period, as well as free configuration of the parameters of the program when it is executed.


Q2: Can you explain the relationship between Create_program and Create_job?

A2: First of all, you should know that creating a program is not a necessary part of a plan, a plan can have no program, but must have a well-defined job;
In addition, program_action This parameter is optional, if the type of program is Pl/sql block, you can specify it when creating a job.

As mentioned above, programs and operations can be separated, so the specific execution (ACTION) of a program can be flexibly determined. It can be run only once, or it can be reused in several different jobs. This makes it very flexible to modify the plan for a job, and you don't need to re-create the Pl/sql block.


Q3: What permissions do I need to run Create_program?

A3: To ensure that create_program can execute smoothly, you also need to create job this system permissions. If a user has the Createany job permission, it can create programs that are owned by any user (except the SYS user)

As with creating a job, a program is established and the default state is inactive, and of course you can explicitly set the enabled parameter to True when you create the program
Activate it.


Q4: Can you introduce the parameters of the Create_program process?
More examples?

A4:
Program_name: Specifies the name of the program;

Program_type: Currently only supports the following three kinds:
Stored_procedure
Plsql_block
Executable

Program_action: (with length limitations, because the type is VARCHAR2)

The name of the stored procedure;
The specific Pl/sql code
Operating system script Name


To see an example of using a pl/sql block
BEGIN
Dbms_scheduler. Create_program (
Program_name => ' LEO. Update_stats ',
Program_type => ' Plsql_block ',

Program_action => ' DECLARE
sUserName VARCHAR2 (30);

Cursor cur is select username from dba_users
where username not in (' SYS ', ' SYSTEM ', ' Sysman ', ' Dbsnmp ')
and account_status= ' OPEN ' Andsubstr (username,1,5) <> ' mgmt_ ';

BEGIN

OPEN cur;
FETCH cur into susername;

While Cur%found
LOOP
Dbms_stats. Gather_schema_stats (sUserName);

FETCH cur into susername;
End LOOP;

Close cur;

end; ');

End;
/

The example above creates a program called "Update_stats", which is a pl/sql block that completes the task of updating statistics for users who are not systems. On this basis you can customize a reasonable plan to execute the program regularly.



4. Configure the program's parameters

Let's take a look at an example

Begin
Dbms_scheduler.create_program (
Program_name=> ' LEO. Update_stats_2 ',
Program_action=> ' LEO. Update_schema_status ');
Program_type=> ' Stored_procedure ',
Number_of_arguments => 1,
Enabled => TRUE);

Dbms_scheduler.define_program_argument (
Program_name=> ' Update_stats_2 ',
Argument_name => ' schema_name ',
Argument_position => 1,
Argument_type => ' varchar2 ',
Default_value => ' HR ');
End
/

Does this routine look like the example above? Yes, the difference between them is just
In this example, the type of the program is the stored procedure, not the Pl/sql block.

Explain:
Use the Define_program_argument procedure to define the parameters required by a program. There are two points to explain:

1 if the program uses parameters, it must be specified in advance, so that the program can be used when the job is in effect;

2 Defining the parameters of the program does not change the activation property of the program. That is, if a program is inactive, running the define_program_argument process does not automatically activate the program.


About permissions:
By default, only the owner of program can modify the programs that are created, and if the user is granted ALTER permission or create ANYJOB permission, it is possible to modify the program that is the owner of another user.



5. Create a plan (Schedule)

In fact, if you already know how to create a job and a program, you have mastered how to create a plan. The additional work you need to do is to specify the start time, end time, repetition frequency, etc. of the plan.

Take a look at an example

Begin
Dbms_scheduler.create_job (
Job_name=> ' Leo. Update_stats_job ',
Program_name=> ' Leo. Update_stats_2 ',
Start_date=> ' 2005-06-20 11:00.00.000000 pm+8:00 ',
Repeat_interval=> ' freq=monthly;interval=1 ',
End_date=> ' 2006-06-20 11:00.00.000000 pm+8:00 ',
Comments=> ' monthly statistics collectionjob ');
End
/

Start_date and end_date These two parameters need to be explained: their data types
is timestamp, so precise time and time zones are required. The time format inherits the value of Nls_date_format this initialization parameter.

The next section describes:

1 Configure the operation parameters;
2 Create/Use/Manage Job, program, and plan required system permissions.

Some supplementary
1. In 10G R2, if the type of job created with Dbms_scheduler is executable, createexternal job permissions are required.

2. Before using Create_job or create_schedule, please check
Nls_date_language, Nls_date_format,
Nls_timestamp_format, Nls_timestamp_tz_format
The value of such parameters, modified by the ALTER session command

For example:
Sql> select * from Nls_session_parameters;

Alter session set nls_date_language= ' American ';
Alter session set nls_date_format= ' dd-mm-yyyy hh24:mi:ss ';
Alter session set nls_timestamp_tz_format= ' Dd-mm-yyyy HH:MI:SS. Ffam TZR '

Begin
Dbms_scheduler.create_job (
Job_name=> ' Zip_emlog ',
Job_type=> ' executable ',
Job_action => '/home/leo/zip_log.sh ',
Enabled=>true,
Start_date=> ' 03-07-2005 9:30:00 PM + 8:00 ',
Repeat_interval=> ' freq=minutely;interval=30 ',
End_date=> ' 31-07-2005 9:30:00 PM + 8:00 ',
Comments=> ' Get a latest EM log copy and compress itevery minutes ');
End
/

==========================================

job creation in Oracle

==========================================

Usage Demo:
Dbms_job. SUBMIT (: Jobno,//job number
' Your_procedure ',//The process to be performed
Trunc (sysdate) +1/24,//Next Execution time
' Trunc (sysdate) +1/24+1 '//each time interval
);
Delete Job:dbms_job.remove (jobno);
Modify the action to be performed: Job:dbms_job.what (Jobno,what);
Modify the next execution time: Dbms_job.next_date (job,next_date);
Modification interval: dbms_job.interval (job,interval);
Stop Job:dbms.broken (job,broken,nextdate);
Start Job:dbms_job.run (jobno);

Attached: Call statement and parameter description:

Dbms_job.submit (Job out Binary_integer,

What in Archar2,
Next_date in Date,
Interval in varchar2,
No_parse in Boolean)
which
Job: Output variable, is the number of this task in the task queue;
What: the name of the task to be executed and its input parameters; Note that the task name is followed by a semicolon, in the following example:' my_procedure; '
Next_date: Time of task execution;
Interval: The time interval at which the task is performed.

==========================================

first, create a stored procedure

Create or Replaceprocedure my_procedure
Is
--the cursor is used here.
Cursor cur is
SELECT * from users where length (name) > 10;
Begin
For user in Cur
Loop
Insert into users_tempvalues (USER.ID,USER.PASSWD);
End Loop;
End

Second, create a job

variable job_num number;
Begin
Dbms_job.submit (: Job_num, ' my_procedure;', Sysdate, ' sysdate+1 ');
Commit
End;

third, run the job

begin
Dbms_job.run (: Job_num);
End;

Four, query the job

Select Priv_user,job,whatfrom user_jobs;

v. Deletion of jobs

begin
Dbms_job.remove (: Job_num);
End

Six, Time

sysdate+1/1440Execute every 1 minutes
sysdate+5/1440Execute every 5 minutes
trunc (sysdate+1) +2/24The next day at 2 O ' (early morning) to perform a
' sysdate +7 'Executed 7 days after the last execution
' sysdate +1/48 'Execute every half hour
' Next_day (TRUNC (sysdate), ' MONDAY ') +15/24 '3 o'clock in the afternoon every Monday.
' Next_day (add_months (TRUNC (sysdate, ' Q '), 3), ' Thursday ') 'First Thursday of each quarter

Turn from: http://blog.sina.com.cn/s/blog_4449f2f90100j63b.html