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

Editor to share with you how to create and manage Schedule in ORACLE Scheduler, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

The new SCHEDULER in 10g may indeed make many new contacts feel dizzy, with too many new concepts compared to the previous jobs,SCHEDULER. Jobs, for example, can still be understood as jobs in the previous version, but it is more powerful (note that ordinary jobs can still be used in 10g, which is nonsense, and I believe friends who read this article should still be using it). For example, program refers to the program that is running (mentioning what to do), such as schedule, which I translate into scheduling (job I translated into tasks). Define the frequency or cycle of execution.

3.1 create and manage Schedules

Schedule, literally translated into Chinese, should be understood as scheduling. From the name, it is a logical entity (logic, entity, and contradiction), that is, when schedule is created, there must be this object in the database, but this object is used to describe the execution cycle of job.

You can create a schedule through the DBMS_SCHEDULER.CREATE_SCHEDULE process, which supports the following parameters:

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 each parameter are as follows:

SCHEDULE_NAME: specify the schedule name. Note that the name cannot be duplicated.

START_DATE: specifies the start time of the schedule, which can be empty. If it is empty, it means that the schedule is temporarily unavailable.

REPEAT_INTERVAL: specifies the frequency or period of execution of the schedule.

END_DATE: specifies the end time of the schedule, which can be empty. If it is empty, it means that the schedule will continue.

COMMENTS: comment information.

Among them, the more technical parameter is the REPEAT_INTERVAL parameter, and we should not be too unfamiliar with this parameter, because the previous introduction of Jobs has also mentioned the parameter of the same name. The function of the REPEAT_INTERVAL parameter in Schedules is exactly the same as that of the REPEAT_INTERVAL parameter in Jobs, and even the parameter format is exactly the same.

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 time period of the interval. Optional parameters are: YEARLY, MONTHLY, WEEKLY, DAILY, HOURLY, MINUTELY, and SECONDLY, which represent year, month, week, day, hour, minute, second and other units, respectively.

The INTERVAL keyword is used to specify the frequency of intervals, with values ranging from 1 to 99.

For example, specifying REPEAT_INTERVAL= > 'FREQ=DAILY;INTERVAL=1'; means that it is executed once a day, and changing INTERVAL to 7 means that it is executed every 7 days, which is equivalent to FREQ=WEEKLY;INTERVAL=1.

Next, create a schedule that specifies the frequency of scheduling once a week, and execute the script 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 schedules that has been created, you can use the * _ SCHEDULER_SCHEDULES view (including DBA_,ALL_,USER_). For example, to view the schedules owned by the current user, execute the statement as follows:

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 property, you will also use the DBMS_SCHEDULER.SET_ATTRIBUTE procedure. The calling method of this procedure has been demonstrated many times before, so I will not repeat the example here, just to illustrate that for schedule, the attributes that can be modified include: REPEAT_INTERVAL, COMMENTS, END_DATE, START_DATE, and EVENT_SPEC.

As for deleting schedule, it can be as simple as executing the DBMS_SCHEDULER.DROP_SCHEDULE process, such as:

SQL > EXEC DBMS_SCHEDULER.DROP_SCHEDULE ('MY_FIRST_SCHEDULE')

PL/SQL procedure successfully completed.

These are all the contents of the article "how to create and manage Schedule in ORACLE Scheduler". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.