Learn more about Oracle schedle feature (4) create and manage schedule

Iii. Use schedules

　　10gNewly releasedSchedulerIt may make many new friends feel dizzy. Compared with previous jobs, scheduler has many new concepts. For example, jobs can still be understood as jobs in earlier versions, but they are more powerful (note that normal jobs can still be used in 10 Gb, Which is nonsense, I believe that my friends who read this article should still use it like this at present. For example, program refers to the running program (submit the ticket), such as schedule, I translate it into scheduling (job I translate it into tasks) and define the frequency or cycle of execution.

3.1 create and manage schedule S.

Schedule is a logical entity (logical, physical, and conflicting). After schedule is created,DatabaseBut this pair is used to describe the execution cycle of the job.

You can create schedule through the dbms_scheduler.create_schedule process. The parameters supported in this process are as follows:

SQL> DESC dbms_scheduler.create_schedule;

Parameter type mode default?

---------------------------------------------------

Schedule_name varchar2 in

Start_date timestamp with Time Zone in Y

Repeat_interval varchar2 in

End_date timestamp with Time Zone in Y

Comments varchar2 in Y

The meanings of parameters are as follows:

• Schedule_name: specify the name of schedule. Note that the name cannot be repeated.
• Start_date: Specifies the start time of the scheduling. It can be empty. If it is empty, the scheduling is unavailable for the time being.
• Repeat_interval: Specifies the execution frequency or cycle of the scheduling.
• End_date: Specifies the scheduling end time. It can be empty. If it is empty, the scheduling will continue.
• Comments: comments.

Among them, there areTechnologyThis parameter contains the repeat_interval parameter. This parameter should not be too unfamiliar to everyone, because the parameters with the same name have also been mentioned in the previous introduction of jobs, the repeat_interval parameter in schedules has the same functions and even the same format as the repeat_interval parameter in jobs.

The syntax structure of the repeat_interval parameter is much more complex. The most important keywords are freq and interval.

• The freq keyword is used to specify the interval. Optional parameters include: yearly, monthly, weekly, daily, hourly, minutely, and secondly, year, month, week, day, hour, minute, second, and other units.
• The interval keyword is used to specify the interval frequency. The value range of the specified value ranges from 1 to 99.

For example, if repeat_interval => 'freq = daily; interval = 1' is specified, the task is executed once a day. If you change interval to 7, the task is executed once every 7 days, the effect is equivalent to freq = weekly; interval = 1.

Next, create a schedule and specify the frequency of scheduling once a week. The execution script is as follows:

SQL> begin

2 dbms_scheduler.create_schedule (

3 schedule_name => 'my _ first_schedule ',

4 start_date => sysdate,

5 repeat_interval => 'freq = weekly; interval = 1 ',

6 comments => 'every 1 weeks ');

7 end;

8/

PL/SQL procedure successfully completed.

To query the created schedules, you can use the * _ scheduler_schedules view (including DBA _, all _, user _). For example, to view the schedules owned by the current user, run the following statement:

SQL> select schedule_name, repeat_interval from user_scheduler_schedules;

Schedule_name repeat_interval

------------------------------------------------------------

My_first_schedule freq = weekly; interval = 1

If you want to modify the schedule attribute, you also use the dbms_scheduler.set_attribute process. The call method of this process has been demonstrated many times before. Here we will not repeat the example here. Just one thing is worth noting. For schedule, attributes that can be modified include repeat_interval, comments, end_date, start_date, and event_spec.

To delete schedule, you can simply execute the dbms_scheduler.drop_schedule process. For example:

SQL> exec dbms_scheduler.drop_schedule ('My _ first_schedule ');

PL/SQL procedure successfully completed.

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