The DBMS_SCHEDULER
package provides a collection of scheduling functions and procedures that are callable from any PL/SQL program.
See Also:
Oracle Database Administrator's Guide for more information regarding how to useDBMS_SCHEDULER
This chapter contains the following topics:
Rules and Limits
Operational Notes
This section contains topics which relate to using the DBMS_SCHEDULER
package.
The following rules apply when using the DBMS_SCHEDULER
package:
Only SYS
can do anything in SYS
schema.
Several of the procedures accept comma-delimited lists of object names. When a list of names is provided, the Scheduler will stop executing the list on the very first object that returns an error. This means that the Scheduler will not perform the task on the objects in the list after the one that caused the error. For example, consider the statement DBMS_SCHEDULER.STOP_JOB ('job1, job2, job3, sys.jobclass1, sys.jobclass2, sys.jobclass3');
If job3
could not be stopped, then job1
and job2
will be stopped, but the jobs in jobclass1
, jobclass2
, and jobclass3
will not be stopped.
Performing an action on an object that does not exist returns a PL/SQL exception stating that the object does not exist.
The Scheduler uses a rich calendaring syntax to enable you to define repeating schedules, such as "every Tuesday and Friday at 4:00 p.m." or "the second Wednesday of every month." This calendaring syntax is used in calendaring expressions in the repeat_interval
argument of a number of package subprograms. Evaluating a calendaring expression results in a set of discrete timestamps.
See Oracle Database Administrator's Guide for examples of the calendaring syntax.
The calendaring syntax is as follows:
repeat_interval = regular_schedule | combined_schedule regular_schedule = frequency_clause [";" interval_clause] [";" bymonth_clause] [";" byweekno_clause] [";" byyearday_clause] [";" bydate_clause] [";" bymonthday_clause] [";" byday_clause] [";" byhour_clause] [";" byminute_clause] [";" bysecond_clause] [";" bysetpos_clause] [";" include_clause] [";" exclude_clause] [";" intersect_clause][";" periods_clause] [";" byperiod_clause] combined_schedule = schedule_list [";" include_clause] [";" exclude_clause] [";" intersect_clause]
frequency_clause = "FREQ" "=" ( predefined_frequency | user_defined_frequency ) predefined_frequency = "YEARLY" | "MONTHLY" | "WEEKLY" | "DAILY" | "HOURLY" | "MINUTELY" | "SECONDLY" user_defined_frequency = named_schedule interval_clause = "INTERVAL" "=" intervalnum intervalnum = 1 through 99 bymonth_clause = "BYMONTH" "=" monthlist monthlist = monthday ( "," monthday)* month = numeric_month | char_month numeric_month = 1 | 2 | 3 ... 12 char_month = "JAN" | "FEB" | "MAR" | "APR" | "MAY" | "JUN" | "JUL" | "AUG" | "SEP" | "OCT" | "NOV" | "DEC" byweekno_clause = "BYWEEKNO" "=" weeknumber_list weeknumber_list = weeknumber ( "," weeknumber)* weeknumber = [minus] weekno weekno = 1 through 53 byyearday_clause = "BYYEARDAY" "=" yearday_list yearday_list = yearday ( "," yearday)* yearday = [minus] yeardaynum yeardaynum = 1 through 366 bydate_clause = "BYDATE" "=" date_list date_list = date ( "," date)* date = [YYYY]MMDD [ offset | span ] bymonthday_clause = "BYMONTHDAY" "=" monthday_list monthday_list = monthday ( "," monthday)* monthday = [minus] monthdaynum monthdaynum = 1 through 31 byday_clause = "BYDAY" "=" byday_list byday_list = byday ( "," byday)* byday = [weekdaynum] day weekdaynum = [minus] daynum daynum = 1 through 53 /* if frequency is yearly */ daynum = 1 through 5 /* if frequency is monthly */ day = "MON" | "TUE" | "WED" | "THU" | "FRI" | "SAT" | "SUN" byhour_clause = "BYHOUR" "=" hour_list hour_list = hour ( "," hour)* hour = 0 through 23 byminute_clause = "BYMINUTE" "=" minute_list minute_list = minute ( "," minute)* minute = 0 through 59 bysecond_clause = "BYSECOND" "=" second_list second_list = second ( "," second)* second = 0 through 59 bysetpos_clause = "BYSETPOS" "=" setpos_list setpos_list = setpos ("," setpos)* setpos = [minus] setpos_num setpos_num = 1 through 9999 include_clause = "INCLUDE" "=" schedule_list exclude_clause = "EXCLUDE" "=" schedule_list intersect_clause = "INTERSECT" "=" schedule_list schedule_list = schedule_clause ("," schedule_clause)* schedule_clause = named_schedule [ offset ] named_schedule = [schema "."] schedule periods_clause = "PERIODS" "=" periodnum byperiod_clause = "BYPERIOD" "=" period_list period_list = periodnum ("," periodnum)* periodnum = 1 through 100 offset = ("+" | "-") ["OFFSET:"] duration_val span = ("+" | "-" | "^") "SPAN:" duration_val duration_val = dur-weeks | dur_days dur_weeks = numofweeks "W" dur_days = numofdays "D" numofweeks = 1 through 53 numofdays = 1 through 376 minus = "-"
In calendaring syntax, * means 0 or more.
Table 93-1 Values for repeat_interval
Name | Description |
---|---|
|
This specifies the type of recurrence. It must be specified. The possible predefined frequency values are YEARLY, MONTHLY, WEEKLY, DAILY, HOURLY, MINUTELY, and SECONDLY. Alternatively, specifies an existing schedule to use as a user-defined frequency. |
|
This specifies a positive integer representing how often the recurrence repeats. The default is 1, which means every second for secondly, every day for daily, and so on. The maximum value is 999. |
|
This specifies which month or months you want the job to execute in. You can use numbers such as 1 for January and 3 for March, as well as three-letter abbreviations such as FEB for February and JUL for July. |
|
This specifies the week of the year as a number. It follows ISO-8601, which defines the week as starting with Monday and ending with Sunday; and the first week of a year as the first week, which is mostly within the Gregorian year. That last definition is equivalent to the following two variants: the week that contains the first Thursday of the Gregorian year; and the week containing January 4th. The ISO-8601 week numbers are integers from 1 to 52 or 53; parts of week 1 may be in the previous calendar year; parts of week 52 may be in the following calendar year; and if a year has a week 53, parts of it must be in the following calendar year. As an example, in the year 1998 the ISO week 1 began on Monday December 29th, 1997; and the last ISO week (week 53) ended on Sunday January 3rd, 1999. So December 29th, 1997, is in the ISO week 1998-01; and January 1st, 1999, is in the ISO week 1998-53.
Examples of invalid specifications are |
|
This specifies the day of the year as a number. Valid values are 1 to 366. An example is 69, which is March 10 (31 for January, 28 for February, and 10 for March). 69 evaluates to March 10 for non-leap years and March 9 in leap years. -2 will always evaluate to December 30th independent of whether it is a leap year. |
|
This specifies a list of dates, where each date is of the form
The following
The plus sign in front of the Offsets adjust the supplied date by adding or subtracting n days or weeks. |
|
This specifies the day of the month as a number. Valid values are 1 to 31. An example is 10, which means the 10th day of the selected month. You can use the minus sign (-) to count backward from the last day, so, for example, |
|
This specifies the day of the week from Monday to Sunday in the form MON, TUE, and so on. Using numbers, you can specify the 26th Friday of the year, if using a YEARLY frequency, or the 4th THU of the month, using a MONTHLY frequency. Using the minus sign, you can say the second to last Friday of the month. For example, |
|
This specifies the hour on which the job is to run. Valid values are 0 to 23. As an example, 10 means 10 a.m. |
|
This specifies the minute on which the job is to run. Valid values are 0 to 59. As an example, 45 means 45 minutes past the chosen hour. |
|
This specifies the second on which the job is to run. Valid values are 0 to 59. As an example, 30 means 30 seconds past the chosen minute. |
|
This selects one or more items by position in the list of timestamps that result after the whole calendaring expression is evaluated. It is useful for requirements such as running a job on the last workday of the month. Rather than attempting to express this with the other FREQ=MONTHLY; BYDAY=MON,TUE,WED,THU,FRI; BYSETPOS=-1 Valid values are 1 through 9999. A negative number selects an item from the end of the list (-1 is the last item, -2 is the next to last item, and so on) and a positive number selects from the front of the list. The The |
|
This includes one or more named schedules in the calendaring expression. That is, the set of timestamps defined by each included named schedule is added to the results of the calendaring expression. If an identical timestamp is contributed by both an included schedule and the calendaring expression, it is included in the resulting set of timestamps only once. The named schedules must have been defined with the |
|
This excludes one or more named schedules from the calendaring expression. That is, the set of timestamps defined by each excluded named schedule is removed from the results of the calendaring expression. The named schedules must have been defined with the |
|
This specifies an intersection between the calendaring expression results and the set of timestamps defined by one or more named schedules. Only the timestamps that appear both in the calendaring expression and in one of the named schedules are included in the resulting set of timestamps. For example, assume that the named schedule 3/31/2005, 6/30/2005, 9/30/2005, 12/31/2005 The following calendaring expression results in the these dates: 3/31/2005, 4/30/2005, 6/30/2005, 9/30/2005, 12/31/2005 FREQ=MONTHLY; BYMONTHDAY=-1; INTERSECT=last_sat,end_qtr In this example, the terms |
|
This identifies the number of periods that together form one cycle of a user defined frequency. It is used in the FREQ=YEARLY;BYDATE=0301,0601,0901,1201;PERIODS=4 |
|
This selects periods from a user defined frequency. For example, if a main schedule names a user defined frequency schedule that defines the fiscal quarters shown in the previous example, the clause |
There are two ways to combine schedules:
Using a combined schedule expression, which is a list of individual schedules
For example, to create a schedule for all company holidays, you provide a list of individual schedules, where each schedule in the list defines a single holiday. The Scheduler evaluates each individual schedule, and then returns a union of the timestamps returned by each individual schedule. You can follow the initial list of individual schedules with include
, exclude
, and intersect
clauses to create more complex combinations.
Embedding other schedules into the main schedule using include
, exclude
, and intersect
clauses
With this method, the embedded schedules inherit certain attributes from the main schedule.
Timestamps generated by the INCLUDE
clause that fall into periods that are skipped by the main schedule are ignored. This is the case when the main schedule skips periods due to the INTERVAL
clause, the BYPERIOD
clause, or the BYMONTH
clause for freq=monthly
.
Days that are added by the INCLUDE
clause follow the hourly/minutely/secondly execution pattern of the main schedule.
When the INCLUDE
clause is present, no date-specific defaults are retrieved from the start date (but time-specific defaults can be). (See "Start Dates and Repeat Intervals", later in this section.) For example, a repeat_interval
of FREQ=MONTHLY;INCLUDE=HOLIDAY
executes only on holidays and not on the month/day defaults retrieved from the start date.
The following is an example:
BEGIN dbms_scheduler.create_schedule('embed_sched', repeat_interval => 'FREQ=YEARLY;BYDATE=0130,0220,0725'); dbms_scheduler.create_schedule('main_sched', repeat_interval => 'FREQ=MONTHLY;INTERVAL=2;BYMONTHDAY=15;BYHOUR=9,17;INCLUDE=embed_sched'); END; /
In this example, the dates 1/30, 2/20, and 7/25 are added to the main schedule. However, the Scheduler does not include dates that fall in months that are skipped by the INTERVAL
clause. If the start date of the main schedule is 1/1/2005, then 2/20 isn't added. On the dates that are added, the embedded schedule follows the execution pattern of the main schedule: jobs are executed at 9:00 a.m. and 5:00 p.m. on 1/30 and 7/25. If the embedded schedule does not itself have a start date, it inherits the start date from the main schedule.
Instead of using predefined frequencies like DAILY
, WEEKLY
, MONTHLY
, and so on, you can create your own frequencies by creating a schedule that returns the start date of each period. For example, the following repeat_interval
expression is used in a schedule named fiscal_year
that defines the start of each quarter in a fiscal year:
FREQ=YEARLY;BYDATE=0301,0601,0901,1201;PERIODS=4
To return the last Wednesday of every quarter, you create a schedule (the "main schedule") that uses the fiscal_year
schedule as a user defined frequency:
FREQ=fiscal_year;BYDAY=-1WED
Periods in a user defined frequency do not have to be equal in length. In the main schedule, the BYSETPOS
clause and numbered weekdays are recalculated based on the size of each period. To select dates in specific periods, you must use the BYPERIOD
clause in the main schedule. To enable this, the schedule that is used as the user defined frequency must include a PERIODS
clause, and it must set its start date appropriately. The first date returned by this schedule is used as the starting point of period 1.
As another example, assuming work days are Monday through Friday, to get the last work day of the 2nd and 4th quarters of the fiscal year, the repeat_interval
clause in the main schedule is the following:
FREQ=fiscal_year;BYDAY=MON,TUE,WED,THU,FRI;BYPERIOD=2,4;BYSETPOS=-1
Start Dates and Repeat Intervals
The Scheduler retrieves the date and time from the job or schedule start date and incorporates them as defaults into the repeat_interval
. For example, if the specified frequency is yearly and there is no BYMONTH
or BYMONTHDAY
clause in the repeat interval, the month and day on which to run the job are retrieved from the start date. Similarly, if frequency is monthly but there is no BYMONTHDAY
clause in the repeat interval, the day of the month on which to run the job is retrieved from the start date. If present, BYHOUR
, BYMINUTE
, and BYSECOND
defaults are also retrieved from the start date, and used if those clauses are not specified. Note that if the INCLUDE
, EXCLUDE
, or INTERSECT
clauses are present, no date-related defaults are retrieved from the start date, but time-related defaults are.The following are some examples:
start_date: 4/15/05 9:00:00 repeat_interval: freq=yearly is expanded internally to: freq=yearly;bymonth=4;bymonthday=15;byhour=9;byminute=0;bysecond=0
The preceding schedule executes on 04/15/05 9:00:00, 04/15/06 9:00:00, 04/15/07 9:00:00, and so on.
For the next example, assume that schedule S1
has a repeat_interval of FREQ=YEARLY;BYDATE=0701
.
start_date: 01/20/05 9:00:00 repeat_interval: freq=yearly;include=S1 is expanded internally to: freq=yearly;byhour=9;byminute=0;bysecond=0;include=S1
Because an INCLUDE
clause is present, date-related information is not retrieved from the start date. However, time-specific information is, so the preceding schedule executes on 07/01/05 9:00:00, 07/01/06 9:00:00, 07/01/08 9:00:00, and so on.
When using a calendaring expression, consider the following rules:
For a regular schedule (as opposed to a combined schedule), the calendar string must start with the frequency clause. All other clauses are optional and can be put in any order.
All clauses are separated by a semi-colon, and each clause can be present at most once, with the exception of the include
, exclude
, and intersect
clauses.
Spaces are allowed between syntax elements and the strings are case insensitive.
The list of values for a specific BY
clause do not need to be ordered.
When not enough BY
clauses are present to determine what the next date is, this information is retrieved from the start date. For example, "FREQ=YEARLY"
with a start date of 02/15/2003 becomes "FREQ=YEARLY;BYMONTH=FEB; BYMONTHDAY=15"
, which means every year on the 15th of February.
"FREQ=YEARLY;BYMONTH=JAN,JUL"
with start date 01/21/2003 becomes "FREQ=YEARLY;BYMONTH=JAN,JUL;BYMONTHDAY=21",
which means every year on January 21 and July 21.
The byweekno
clause is only allowed if the frequency is YEARLY
. It cannot be used with other frequencies. When it is present, it will return all days in that week number. If you want to limit it to specific days within the week, you have to add a BYDAY
clause. For example, "FREQ=YEARLY;BYWEEKNO=2"
with a start date of 01/01/2003 will return:
01/06/2003, 01/07/2003, 01/08/2003, 01/09/2003, 01/10/2003, 01/11/2003, 01/12/2003, 01/05/2004, 01/06/2004, 01/07/2004, .... and so on.
Note that when the byweekno
clause is used, it is possible that the dates returned are from a year other than the current year. For example, if returning dates for the year 2004 and the calendar string is "FREQ=YEARLY;BYWEEKNO=1,53"
for the specified week numbers in 2004, it will return the dates:
12/29/03, 12/30/03, 12/31/03, 01/01/04, 01/02/04, 01/03/04, 01/04/04, 12/27/04, 12/28/04, 12/29/04, 12/30/04, 12/31/04, 01/01/05, 01/02/05
For those BY
clauses that do not have a consistent range of values, you can count backward by putting a "-" in front of the numeric value. For example, specifying BYMONTHDAY=31
will not give you the last day of every month, because not every month has 31 days. Instead, BYMONTHDAY=-1
will give you the last day of the month.
This is not supported for BY
clauses that are fixed in size. In other words, BYMONTH
, BYHOUR
, BYMINUTE
, and BYSECOND
are not supported.
The basic values for the BYDAY
clause are the days of the week. When the frequency is YEARLY
, or MONTHLY
, you are allowed to specify a positive or negative number in front of each day of the week. In the case of YEARLY
, BYDAY=40MON
, indicates the 40th Monday of the year. In the case of MONTHLY, BYDAY=-2SAT
, indicates the second to last Saturday of the month.
Note that positive or negative numbers in front of the weekdays are not supported for other frequencies and that in the case of yearly, the number ranges from -53 ... -1, 1 ... 53, whereas for the monthly frequency it is limited to -5 ... -1, 1... 5.
If no number is present in front of the weekday it specifies, every occurrence of that weekday in the specified frequency.
The first day of the week is Monday.
Repeating jobs with frequencies smaller than daily follow their frequencies exactly across daylight savings adjustments. For example, suppose that a job is scheduled to repeat every 3 hours, the clock is moved forward from 1:00 a.m. to 2:00 a.m., and the last time the job ran was midnight. Its next scheduled time will be 4:00 a.m. Thus, the 3 hour period between subsequent job runs is retained. The same applies when the clock is moved back. This behavior is not the case for repeating jobs that have frequencies of daily or larger. For example, if a repeating job is supposed to be executed on a daily basis at midnight, it will continue to run at midnight if the clock is moved forward or backward. When the execution time of such a daily (or larger frequency) job happens to fall inside a window where the clock is moved forward, the job executes at the end of the window.
The calendaring syntax does not allow you to specify a time zone. Instead the Scheduler retrieves the time zone from the start_date
argument. If jobs must follow daylight savings adjustments you must make sure that you specify a region name for the time zone of the start_date
. For example specifying the start_date
time zone as 'US/Eastern
' in New York will make sure that daylight saving adjustments are automatically applied. If instead the time zone of the start_date
is set to an absolute offset, such as '-5:00
', daylight savings adjustments are not followed and your job execution will be off by an hour half of the year.
When start_date
is NULL
, the Scheduler will determine the time zone for the repeat interval as follows:
It will check whether the session time zone is a region name. The session time zone can be set by either:
Issuing an ALTER
SESSION
statement, for example:
SQL> ALTER SESSION SET time_zone = 'Asia/Shanghai';
Setting the ORA_SDTZ
environment variable.
If the session time zone is an absolute offset instead of a region name, the Scheduler will use the value of the DEFAULT_TIMEZONE
Scheduler attribute. For more information, see the SET_SCHEDULER_ATTRIBUTE Procedure.
If the DEFAULT_TIMEZONE
attribute is NULL
, the Scheduler will use the time zone of systimestamp
when the job or window is enabled.
The BYSETPOS
clause is the last clause to be evaluated. It is processed after all other BY
clauses and the INCLUDE
, EXCLUDE
and INTERSECT
clauses have been evaluated.
The INTERVAL
clause does not change the size of the period to which the BYSETPOS
clause is applied. For example, when the frequency is set to monthly and interval is set to 3, the list of timestamps to which BYSETPOS
is applied is generated from a month, not a quarter. The only impact of the INTERVAL
clause is to cause months to be skipped. However, you can still select the second to last workday of the quarter like this:
FREQ=MONTHLY;INTERVAL=3;BYDAY=MON,TUE,WED,THU,FRI;BYSETPOS=-2
provided that you set the start date in the right month. This example returns the next to last workday of a month, and repeats once a quarter.
To get consistent results, the set to which BYSETPOS
is applied is determined from the beginning of the frequency period independently of when the evaluation occurs. Whether the Scheduler evaluates
FREQ=MONTHLY;BYDAY=MON,TUE,FRI;BYSETPOS=1,3
on 01/01/2004 or 01/15/2004, in both cases the expression evaluates to Friday 01/02/2004, and Tuesday 01/06/2004. The only difference is that when the expression is evaluated on 01/15/2004, the Scheduler determines that there are no matches in January because the timestamps found are in the past, and it moves on to the matches in the next month, February.
If dates in the BYDATE
clause do not have their optional year component, the job runs on those dates every year.
The job execution times on the included dates are derived from the BY
clauses in the calendaring expression. For example, if repeat_interval
is defined as
freq=daily;byhour=8,13,18;byminute=0;bysecond=0;bydate=0502,0922
then the execution times on 05/02 and 09/22 are 8:00 a.m., 1:00 p.m., and 6:00 p.m.
Excluded dates without a time component are 24 hour periods. All timestamps that fall on an excluded date are removed. In the following example, jan_fifteen
is a named schedule that resolves to the single date of 01/15:
freq=monthly;bymonthday=15,30;byhour=8,13,18;byminute=0;bysecond=0; exclude=jan_fifteenth
In this case, all three instances of the job are removed for 01/15.
You can adjust the dates of individual named schedules by adding positive offsets to them. For example, to execute JOB2
exactly 15 days after every occurrence of JOB1
, add +OFFSET:15D
to the schedule of JOB1
, as follows:
BEGIN dbms_scheduler.create_schedule('job2_schedule', repeat_interval => 'job1_schedule+OFFSET:15D'); END; /
Note that negative offsets to named schedules are not supported.
Example: Putting It All Together
This example demonstrates the use of user defined frequencies, spans, offsets, and the BYSETPOS
and INCLUDE
clauses. (Note that the OFFSET:
keyword in an offset clause is optional.)
Many companies in the retail industry share the same fiscal year. The fiscal year starts on the Sunday closest to February 1st, and subsequent quarters start exactly 13 weeks later. The fiscal year schedule for the retail industry can be defined as the following:
begin dbms_scheduler.create_schedule('year_start', repeat_interval=> 'FREQ=YEARLY;BYDATE=0201^SPAN:1W;BYDAY=SUN'); dbms_scheduler.create_schedule('retail_fiscal_year', to_timestamp_tz('15-JAN-2005 12:00:00','DD-MON-YYYY HH24:MI:SS'), 'year_start,year_start+13w,year_start+26w,year_start+39w;periods=4'); end; /
The following schedule can be used to execute a job on the 5th day off in the 2nd and the 4th quarters of the retail industry. This assumes that Saturday and Sunday are off days as well as the days in the existing holiday
schedule.
begin dbms_scheduler.create_schedule('fifth_day_off', repeat_interval=> 'FREQ=retail_fiscal_year;BYDAY=SAT,SUN;INCLUDE=holiday; BYPERIOD=2,4;BYSETPOS=5'); end; /
Table 93-2 DBMS_SCHEDULER Package Subprograms
Subprogram | Description |
---|---|
Adds a user as a subscriber to the Scheduler event queue |
|
Adds a window to an existing window group |
|
Alters specified steps of a chain |
|
Alters specified steps of a running chain |
|
Closes an open window prematurely |
|
Copies an existing job |
|
Creates a chain, which is a named series of programs that are linked together for a combined objective |
|
Creates an event schedule, which is a schedule that starts a job based on the detection of an event |
|
Creates a job |
|
Creates a job class, which provides a way to group jobs for resource allocation and prioritization |
|
Creates a program |
|
Creates a schedule |
|
Creates a window, which provides a way to automatically activate different resource plans at different times |
|
Creates a window group |
|
Defines a program argument whose value is of a complex type and must be passed encapsulated in an |
|
Adds or replaces a chain step and associates it with an event schedule or inline event. See also: |
|
Adds a rule to an existing chain |
|
Defines a chain step, which can be a program or another (nested) chain. See also: |
|
Defines a special metadata argument for the program. You can retrieve specific metadata through this argument |
|
Defines a program argument whose value can be passed as a string literal to the program |
|
Disables a program, job, chain, window, or window group |
|
Drops an existing chain |
|
Removes a rule from an existing chain |
|
Drops a chain step |
|
Drops a job or all jobs in a job class |
|
Drops a job class |
|
Drops a program |
|
Drops a program argument |
|
Drops a schedule |
|
Drops a window |
|
Drops a window group |
|
Enables a program, job, chain, window, or window group |
|
Evaluates the calendar string and tells you what the next execution date of a job or window will be |
|
Forces reevaluation of the rules of a running chain to trigger any rules for which the conditions have been satisfied |
|
Generates a unique name for a job. This enables you to identify jobs by adding a prefix, so, for example, Sally's jobs would be named |
|
Retrieves the value of an attribute of an object |
|
Retrieves the value of a Scheduler attribute |
|
Opens a window prematurely. The window is opened immediately for the duration |
|
Purges specific rows from the job and window logs |
|
Unsubscribes a user from the Scheduler event queue |
|
Removes a window from an existing window group. This fails if the specified window is not a member of the given group |
|
Resets the current value assigned to an argument defined with the associated program |
|
Immediately runs a chain by creating a run-once job |
|
Runs a job immediately |
|
Changes an attribute of a job, schedule, or other Scheduler object |
|
Changes an attribute of an object to |
|
Sets the value of a job argument encapsulated in an |
|
Sets the value of a job argument |
|
Sets the value of a Scheduler attribute |
|
Stops a currently running job or all jobs in a job class |
This procedure adds a user as a subscriber to the Scheduler event queue SYS.SCHEDULER$_EVENT_QUEUE
, and grants the user permission to dequeue from this queue using the designated agent.
DBMS_SCHEDULER.ADD_EVENT_QUEUE_SUBSCRIBER ( subscriber_name IN VARCHAR2 DEFAULT NULL);
Table 93-3 ADD_EVENT_QUEUE_SUBSCRIBER Procedure Parameters
Parameter | Description |
---|---|
|
Name of the Oracle Streams Advanced Queuing (AQ) agent to be used to subscribe to the Scheduler event queue. If |
The subscription is rule-based. The rule permits the user to see only events raised by jobs that the user owns, and filters out all other messages. If an AQ agent with the same name already exists, an error is raised.
This procedure adds one or more windows to an existing window group.
DBMS_SCHEDULER.ADD_WINDOW_GROUP_MEMBER ( group_name IN VARCHAR2, window_list IN VARCHAR2);
Table 93-4 ADD_WINDOW_GROUP_MEMBER Procedure Parameters
Parameter | Description |
---|---|
|
The name of the window group |
|
The name of the window or windows |
If an already open window is added to a window group, the Scheduler will not pick up jobs that point to this window group until the next window in the window group opens.
Adding a window to a group requires the MANAGE
SCHEDULER
privilege.
Note that a window group cannot be a member of another window group.
This procedure alters specified steps of a chain. This affects all future runs of the specified steps.
DBMS_SCHEDULER.ALTER_CHAIN ( chain_name IN VARCHAR2, step_name IN VARCHAR2, attribute IN VARCHAR2, value IN BOOLEAN);
Table 93-5 ALTER_CHAIN Procedure Parameters
Parameter | Description |
---|---|
|
The name of the chain to alter |
|
The name of the step or a comma-separated list of steps to alter. This cannot be |
|
The attribute of the steps to change. This must be one of: '
|
|
The value to set for the attribute. This must be one of: |
Altering a chain requires ALTER
privileges on the chain either by being the owner of the chain, or by having the ALTER
object privilege on the chain or by having the CREATE
ANY
JOB
system privilege.
This procedure alters specified steps of a running chain. This will affect only steps of this running instance of the chain.
DBMS_SCHEDULER.ALTER_RUNNING_CHAIN ( job_name IN VARCHAR2, step_name IN VARCHAR2, attribute IN VARCHAR2, value IN [BOOLEAN|VARCHAR2]);
Table 93-6 ALTER_RUNNING_CHAIN Procedure Parameters
Parameter | Description |
---|---|
|
The name of the job that is running the chain |
|
The name of the step or a comma-separated list of steps to alter. If this is set to |
|
The attribute of the steps to change. This must be one of: '
|
|
The value to set for the attribute. This must be one of: |
Altering a running chain requires alter privileges on the job which is running (either by being the owner, or by having ALTER
privileges on the job or by having the CREATE
ANY
JOB
system privilege).
This procedure closes an open window prematurely. A closed window means that it is no longer in effect. When a window is closed, the Scheduler will switch the resource plan to the one that was in effect outside the window or in the case of overlapping windows to another window.
DBMS_SCHEDULER.CLOSE_WINDOW ( window_name IN VARCHAR2);
Table 93-7 CLOSE_WINDOW Procedure Parameters
Parameter | Description |
---|---|
|
The name of the window |
If you try to close a window that does not exist or is not open, an error is generated.
A job that is running will not stop when the window it is running in closes unless the attribute stop_on_window_close
was set to TRUE
for the job. However, the resources allocated to the job may change because the resource plan may change.
When a running job has a window group as its schedule, the job will not be stopped when its window is closed if another window that is also a member of the same window group then becomes active. This is the case even if the job has the attribute stop_on_window_close
set to TRUE
.
Closing a window requires the MANAGE
SCHEDULER
privilege.
This procedure copies all attributes of an existing job to a new job. The new job is created disabled, while the state of the existing job is unaltered.
DBMS_SCHEDULER.COPY_JOB ( old_job IN VARCHAR2, new_job IN VARCHAR2);
Table 93-8 COPY_JOB Procedure Parameters
Parameter | Description |
---|---|
|
The name of the existing job |
|
The name of the new job |
Copying a job requires privileges to create a job in the schema of the new job (the CREATE
JOB
system privilege if it is in the user's own schema, and the CREATE
ANY
JOB
system privilege otherwise). If the old job is not in the user's own schema, then he needs to additionally have ALTER
privileges on the old job or the CREATE
ANY
JOB
system privilege.
This procedure creates a new chain. The chain name can be optionally qualified with a schema name (for example, myschema.myname
).
A chain is always created disabled and must be enabled with the ENABLE Procedure before it can be used.
DBMS_SCHEDULER.CREATE_CHAIN ( chain_name IN VARCHAR2, rule_set_name IN VARCHAR2 DEFAULT NULL, evaluation_interval IN INTERVAL DAY TO SECOND DEFAULT NULL, comments IN VARCHAR2 DEFAULT NULL);
Table 93-9 CREATE_CHAIN Procedure Parameters
Parameter | Description |
---|---|
|
The name of the new chain, which can optionally be qualified with a schema. This must be unique in the SQL namespace, so there must not already be a table or other object with this name and schema. |
|
In the normal case, no rule set should be passed in. The Scheduler will automatically create a rule set and associated empty evaluation context. You then use Advanced users can create a rule set that describes their chain dependencies and pass it in here. This allows greater flexibility in defining rules. For example, conditions can refer to external variables, and tables can be exposed through the evaluation context. If you pass in a rule set, you must ensure that it is in the format of a chain rule set. (For example, all steps must be listed as variables in the evaluation context). If no rule set is passed in, the rule set created will be of the form See Oracle Streams Concepts and Administration for information on rules and rule sets. |
|
If this is |
|
An optional comment describing the purpose of the chain |
Creating a chain in one's own schema requires the CREATE
JOB
system privilege. Creating a chain in a different schema requires the CREATE
ANY
JOB
system privilege. If no rule_set_name
is given, a rule set and evaluation context will be created in the schema that the chain is being created in, so the user will need to have the privileges required to create these objects. See the DBMS_RULE_ADM.CREATE_RULE_SET
and DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT
procedures for more information.
This procedure creates an event schedule, which is used to start a job when a particular event is raised.
DBMS_SCHEDULER.CREATE_EVENT_SCHEDULE ( schedule_name IN VARCHAR2, start_date IN TIMESTAMP WITH TIME ZONE DEFAULT NULL, event_condition IN VARCHAR2, queue_spec IN VARCHAR2, end_date IN TIMESTAMP WITH TIME ZONE DEFAULT NULL, comments IN VARCHAR2 DEFAULT NULL);
Table 93-10 CREATE_EVENT_SCHEDULE Parameters
Parameter | Description |
---|---|
|
This.attribute specifies a unique identifier for the schedule. The name has to be unique in the SQL namespace. For example, a schedule cannot have the same name as a table in a schema. If no name is specified, then an error occurs. |
|
This attribute specifies the date on which this schedule becomes valid. Occurrences of the event before this date are ignored in the context of this schedule. |
|
This is a conditional expression based on the columns of the event source queue table. The expression must have the syntax of an Advanced Queuing rule. Accordingly, you can include user data properties in the expression provided that the message payload is an object type, and that you prefix object attributes in the expression with |
|
This argument specifies the queue into which events that start this particular job will be enqueued (the source queue). If the source queue is a secure queue, the |
|
The date after which jobs will not run and windows will not open. An event schedule that has no
|
|
This attribute specifies an optional comment about the schedule. By default, this attribute is |
This procedure requires the CREATE
JOB
privilege to create a schedule in one's own schema or the CREATE
ANY
JOB
privilege to create a schedule in someone else's schema by specifying schema.schedule_name
. Once a schedule has been created, it can be used by other users. The schedule is created with access to PUBLIC
. Therefore, there is no need to explicitly grant access to the schedule.
This procedure creates a job.
The procedure is overloaded. The different functionality of each form of syntax is presented along with the syntax declaration.
Creates a job in a single call without using an existing program or schedule:
DBMS_SCHEDULER.CREATE_JOB ( job_name IN VARCHAR2, job_type IN VARCHAR2, job_action IN VARCHAR2, number_of_arguments IN PLS_INTEGER DEFAULT 0, start_date IN TIMESTAMP WITH TIME ZONE DEFAULT NULL, repeat_interval IN VARCHAR2 DEFAULT NULL, end_date IN TIMESTAMP WITH TIME ZONE DEFAULT NULL, job_class IN VARCHAR2 DEFAULT 'DEFAULT_JOB_CLASS', enabled IN BOOLEAN DEFAULT FALSE, auto_drop IN BOOLEAN DEFAULT TRUE, comments IN VARCHAR2 DEFAULT NULL);
Creates a job using a named schedule object and a named program object:
DBMS_SCHEDULER.CREATE_JOB ( job_name IN VARCHAR2, program_name IN VARCHAR2, schedule_name IN VARCHAR2, job_class IN VARCHAR2 DEFAULT 'DEFAULT_JOB_CLASS', enabled IN BOOLEAN DEFAULT FALSE, auto_drop IN BOOLEAN DEFAULT TRUE, comments IN VARCHAR2 DEFAULT NULL);
Creates a job using a named program object and an inlined schedule:
DBMS_SCHEDULER.CREATE_JOB ( job_name IN VARCHAR2, program_name IN VARCHAR2, start_date IN TIMESTAMP WITH TIME ZONE DEFAULT NULL, repeat_interval IN VARCHAR2 DEFAULT NULL, end_date IN TIMESTAMP WITH TIME ZONE DEFAULT NULL, job_class IN VARCHAR2 DEFAULT 'DEFAULT_JOB_CLASS', enabled IN BOOLEAN DEFAULT FALSE, auto_drop IN BOOLEAN DEFAULT TRUE, comments IN VARCHAR2 DEFAULT NULL);
Creates a job using a named schedule object and an inlined program:
DBMS_SCHEDULER.CREATE_JOB ( job_name IN VARCHAR2, schedule_name IN VARCHAR2, job_type IN VARCHAR2, job_action IN VARCHAR2, number_of_arguments IN PLS_INTEGER DEFAULT 0, job_class IN VARCHAR2 DEFAULT 'DEFAULT_JOB_CLASS', enabled IN BOOLEAN DEFAULT FALSE, auto_drop IN BOOLEAN DEFAULT TRUE, comments IN VARCHAR2 DEFAULT NULL);
Creates a job using an inlined program and an event:
DBMS_SCHEDULER.CREATE_JOB ( job_name IN VARCHAR2, job_type IN VARCHAR2, job_action IN VARCHAR2, number_of_arguments IN PLS_INTEGER DEFAULT 0, start_date IN TIMESTAMP WITH TIME ZONE, event_condition IN VARCHAR2, event_queue IN VARCHAR2, end_date IN TIMESTAMP WITH TIME ZONE, job_class IN VARCHAR2 DEFAULT 'DEFAULT_JOB_CLASS', enabled IN BOOLEAN DEFAULT FALSE, auto_drop IN BOOLEAN DEFAULT TRUE, comments IN VARCHAR2 DEFAULT NULL);
Creates a job using a named program object and an event:
DBMS_SCHEDULER.CREATE_JOB ( job_name IN VARCHAR2, program_name IN VARCHAR2, start_date IN TIMESTAMP WITH TIME ZONE, event_condition IN VARCHAR2, queue_spec IN VARCHAR2, end_date IN TIMESTAMP WITH TIME ZONE, job_class IN VARCHAR2 DEFAULT 'DEFAULT_JOB_CLASS', enabled IN BOOLEAN DEFAULT FALSE, auto_drop IN BOOLEAN DEFAULT TRUE, comments IN VARCHAR2 DEFAULT NULL);
Table 93-11 CREATE_JOB Procedure Parameters
Parameter | Description |
---|---|
|
This attribute specifies the name of the job and uniquely identifies the job. The name has to be unique in the SQL namespace. For example, a job cannot have the same name as a table in a schema. If the job being created will reside in another schema, it must be qualified with the schema name. If |
|
This attribute specifies the type of job that you are creating. If it is not specified, an error is generated. The supported values are:
|
|
This attribute specifies the action of the job. The following actions are possible: For a PL/SQL block, the action is to execute PL/SQL code. These blocks must end with a semi-colon. For example, For a stored procedure, the action is the name of the stored procedure. You have to specify the schema if the procedure resides in another schema than the job. PL/SQL procedures with For an executable, the action is the name of the external executable, including the full path name and any command-line arguments. For a chain, the action is the name of a Scheduler chain object. You have to specify the schema of the chain if it resides in a different schema than the job. If |
|
This attribute specifies the number of arguments that the job expects. The range is 0-255, with the default being 0. |
|
The name of the program associated with this job. If the program is of type |
|
This attribute specifies the first date on which this job is scheduled to start. If For repeating jobs that use a calendaring expression to specify the repeat interval, The Scheduler cannot guarantee that a job will execute on an exact time because the system may be overloaded and thus resources unavailable. |
|
This is a conditional expression based on the columns of the event source queue table. The expression must have the syntax of an Advanced Queuing rule. Accordingly, you can include user data properties in the expression provided that the message payload is an object type, and that you prefix object attributes in the expression with |
|
This argument specifies the queue into which events that start this particular job will be enqueued (the source queue). If the source queue is a secure queue, the |
|
This attribute specifies how often the job should repeat. You can specify the repeat interval by using calendaring or PL/SQL expressions. The expression specified is evaluated to determine the next time the job should run. If |
|
The name of the schedule, window, or window group associated with this job. |
|
This attribute specifies the date after which the job will expire and will no longer be executed. When If no value for The value for |
job_priority |
This attribute designates the priority of a job relative to other jobs in the same job class only. If two jobs in the same class are scheduled to start at the same time, the one with the higher priority takes precedence. Acceptable values are 1 through 5, where 1 is the highest priority. Default value is 3. |
|
This attribute specifies a comment about the job. By default, this attribute is |
|
This attribute specifies whether the job is created enabled or not. The possible settings are |
|
This flag, if
A job is disabled when it has failed If this flag is set to By default, jobs are created with |
Jobs are created disabled by default. You must explicitly enable them so that they will become active and scheduled. Before enabling a job, ensure that all program arguments, if any, are defined, either by defining default values in the program object or by supplying values with the job.
To create a job in your own schema, you need to have the CREATE
JOB
privilege. A user with the CREATE
ANY
JOB
privilege can create a job in any schema. If the job being created will reside in another schema, the job name must be qualified with the schema name. For a job of type EXECUTABLE
(or for a job that points to a program of type EXECUTABLE
), the job owner must have the CREATE EXTERNAL JOB
system privilege before the job can be enabled or run.
Associating a job with a particular class or program requires EXECUTE
privileges for that class or program.
Not all possible job attributes can be set with CREATE_JOB
. Some must be set after the job is created. For example, job arguments must be set with the SET_JOB_ARGUMENT_VALUE Procedures or the SET_JOB_ANYDATA_VALUE Procedures. Other job attributes, such as job_priority
and max_runs
, are set with the SET_ATTRIBUTE Procedure.
Note:
The Scheduler runs event-based jobs for each occurrence of an event that matches the job's event condition. However, events that occur while the job is already running are ignored; the event gets consumed, but does not trigger another run of the job.This procedure creates a job class. Job classes are created in the SYS
schema.
DBMS_SCHEDULER.CREATE_JOB_CLASS ( job_class_name IN VARCHAR2, resource_consumer_group IN VARCHAR2 DEFAULT NULL, service IN VARCHAR2 DEFAULT NULL, logging_level IN PLS_INTEGER DEFAULT DBMS_SCHEDULER.LOGGING_RUNS, log_history IN PLS_INTEGER DEFAULT NULL, comments IN VARCHAR2 DEFAULT NULL);
Table 93-12 CREATE_JOB_CLASS Procedure Parameters
Parameter | Description |
---|---|
|
The name of the class being created. A schema other than This attribute specifies the name of the job class and uniquely identifies the job class. The name has to be unique in the SQL namespace. For example, a job class cannot have the same name as a table in a schema. |
|
This attribute specifies the resource consumer group this class is associated with. A resource consumer group is a set of synchronous or asynchronous sessions that are grouped together based on their processing needs. A job class has a many-to-one relationship with a resource consumer group. The resource consumer group that the job class associates with will determine the resources that will be allocated to the job class. If the resource consumer group that a job class is associated with is dropped, the job class will then be associated with the default resource consumer group. If no resource consumer group is specified, the job class is associated with the default resource consumer group. If the specified resource consumer group does not exist when creating the job class, an error occurs. If |
|
This attribute specifies the database service that the jobs in this class will have affinity to. In a RAC environment, this means that the jobs in this class will only run on those database instances that are assigned to the specific service. If a service is specified, If no service is specified, the job class will belong to the default service, which means it will have no service affinity and any one of the database instances within the cluster might run the job. If the service that a job class belongs to is dropped, the job class will then belong to the default service. If the specified service does not exist when creating the job class, then an error occurs. |
|
This attribute specifies how much information is logged. The three possible options are:
|
|
This enables you to control the amount of logging the Scheduler performs. To prevent the job log and the window log from growing indiscriminately, the Scheduler has an attribute that specifies how much history (in days) to keep. Once a day, the Scheduler will automatically purge all log entries from both the job log as well as the window log that are older than the specified history. The default is 30 days. You can change the default by using the
The range of valid values is 1 through 999. |
|
This attribute is for an optional comment about the job class. By default, this attribute is |
For users to create jobs that belong to a job class, the job owner must have EXECUTE
privileges on the job class. Therefore, after the job class has been created, EXECUTE
privileges must be granted on the job class so that users create jobs belonging to that class. You can also grant the EXECUTE
privilege to a role.
Creating a job class requires the MANAGE
SCHEDULER
system privilege.
This procedure creates a program.
DBMS_SCHEDULER.CREATE_PROGRAM ( program_name IN VARCHAR2, program_type IN VARCHAR2, program_action IN VARCHAR2, number_of_arguments IN PLS_INTEGER DEFAULT 0, enabled IN BOOLEAN DEFAULT FALSE, comments IN VARCHAR2 DEFAULT NULL);
Table 93-13 CREATE_PROGRAM Procedure Parameters
Parameter | Description |
---|---|
|
This attribute specifies a unique identifier for the program. The name has to be unique in the SQL namespace. For example, a program cannot have the same name as a table in a schema. If no name is specified, then an error occurs. |
|
This attribute specifies the type of program you are creating. If it is not specified then you will get an error. There are three supported values for
|
|
This attribute specifies the action of the program. The following actions are possible: For a PL/SQL block, the action is to execute PL/SQL code. These blocks must end with a semi-colon. For example, For a stored procedure, the action is the name of the stored procedure. You have to specify the schema if the procedure resides in another schema than the job. For an executable, the action is the name of the external executable, including the full path name and any command-line arguments. If If it is an anonymous block, special Scheduler metadata may be accessed using the following variable names: |
|
This attribute specifies the number of arguments the program takes. If this parameter is not specified then the default will be 0. A program can have a maximum of 255 arguments. If the |
|
This flag specifies whether the program should be created enabled or not. If the flag is set to |
|
A comment about the program. By default, this attribute is |
To create a program in his own schema, a user needs the CREATE
JOB
privilege. A user with the CREATE
ANY
JOB
privilege can create a program in any schema. A program is created in a disabled state by default (unless the enabled field is set to TRUE
). It cannot be executed by a job until it is enabled.
For other users to use your programs, they must have EXECUTE
privileges, therefore once a program has been created, you have to grant EXECUTE
privileges on it.
This procedure creates a schedule.
DBMS_SCHEDULER.CREATE_SCHEDULE ( schedule_name IN VARCHAR2, start_date IN TIMESTAMP WITH TIMEZONE DEFAULT NULL, repeat_interval IN VARCHAR2, end_date IN TIMESTAMP WITH TIMEZONE DEFAULT NULL, comments IN VARCHAR2 DEFAULT NULL);
Table 93-14 CREATE_SCHEDULE Procedure Parameters
Parameter | Description |
---|---|
|
This attribute specifies a unique identifier for the schedule. The name has to be unique in the SQL namespace. For example, a schedule cannot have the same name as a table in a schema. If no name is specified, then an error occurs. |
|
This attribute specifies the first date on which this schedule becomes valid. For a repeating schedule, the value for If |
|
This attribute specifies how often the schedule should repeat. It is expressed using calendaring syntax. See "Calendaring Syntax" for further information. PL/SQL expressions are not allowed as repeat intervals for named schedules. |
|
The date after which jobs will not run and windows will not open. A non-repeating schedule that has no
|
|
This attribute specifies an optional comment about the schedule. By default, this attribute is |
This procedure requires the CREATE
JOB
privilege to create a schedule in one's own schema or the CREATE
ANY
JOB
privilege to create a schedule in someone else's schema by specifying schema.schedule_name
. Once a schedule has been created, it can be used by other users. The schedule is created with access to PUBLIC
. Therefore, there is no need to explicitly grant access to the schedule.
This procedure creates a recurring time window and associates it with a resource plan. The window can then be used to schedule jobs, which run under the associated resource plan.
The procedure is overloaded.
Creates a window using a named schedule object:
DBMS_SCHEDULER.CREATE_WINDOW ( window_name IN VARCHAR2, resource_plan IN VARCHAR2, schedule_name IN VARCHAR2, duration IN INTERVAL DAY TO SECOND, window_priority IN VARCHAR2 DEFAULT 'LOW', comments IN VARCHAR2 DEFAULT NULL);
Creates a window using an inlined schedule:
DBMS_SCHEDULER.CREATE_WINDOW ( window_name IN VARCHAR2, resource_plan IN VARCHAR2, start_date IN TIMESTAMP WITH TIME ZONE DEFAULT NULL, repeat_interval IN VARCHAR2, end_date IN TIMESTAMP WITH TIME ZONE DEFAULT NULL, duration IN INTERVAL DAY TO SECOND, window_priority IN VARCHAR2 DEFAULT 'LOW', comments IN VARCHAR2 DEFAULT NULL);
Table 93-15 CREATE_WINDOW Procedure Parameters
Parameter | Description |
---|---|
|
This attribute uniquely identifies the window. The name has to be unique in the SQL namespace. All windows are in the |
|
This attribute specifies the resource plan that is automatically activated when the window opens. When the window closes, the system switches to the appropriate resource plan, which in most cases is the resource plan that was in effect before the window opened, but can also be the resource plan of yet another window. Only one resource plan can be associated with a window. It may be If the window is open and the resource plan is dropped, then the resource allocation for the duration of the window is not affected. |
|
This attribute specifies the first date on which this window is scheduled to open. If the value for For repeating windows that use a calendaring expression to specify the repeat interval, the value for |
|
This attribute specifies how long the window will be open for. For example, |
|
The name of the schedule associated with the window. |
|
This attribute specifies how often the window should repeat. It is expressed using the Scheduler's calendaring syntax. See "Calendaring Syntax" for more information. A PL/SQL expression cannot be used to specify the repeat interval for a window. The expression specified is evaluated to determine the next time the window should open. If no repeat_interval is specified, the window will open only once at the specified start date. |
|
This attribute specifies the date after which the window will no longer open. When the value for A non-repeating window that has no value for end_date opens only once for the duration of the window. For a repeating window, if no The |
|
This attribute is only relevant when two windows overlap. Because only one window can be in effect at one time, the window priority will be used to determine which window will be opened. The two possible values for this attribute are ' |
|
This attribute specifies an optional comment about the window. By default, this attribute is |
Creating a window requires the MANAGE
SCHEDULER
privilege. Windows always reside in the SYS
schema.
Scheduler windows are the principal mechanism used to automatically switch resource plans according to a schedule. You can also manually activate a resource plan by using the ALTER SYSTEM SET RESOURCE_MANAGER_PLAN
statement or the DBMS_RESOURCE_MANAGER.SWITCH_PLAN
package procedure. Note that either of these manual methods can also disable resource plan switching by Scheduler windows. For more information, see Oracle Database Administrator's Guide and "SWITCH_PLAN Procedure".
This procedure creates a new window group.
DBMS_SCHEDULER.CREATE_WINDOW_GROUP ( group_name IN VARCHAR2, window_list IN VARCHAR2 DEFAULT NULL, comments IN VARCHAR2 DEFAULT NULL);
Table 93-16 CREATE_WINDOW_GROUP Procedure Parameters
Parameter | Description |
---|---|
|
The name of the window group |
|
A list of the windows assigned to the window group. If a window that does not exist is specified, an error is generated and the window group is not created. Windows can also be added using the Can be |
|
A comment about the window group |
Creating a window group requires the MANAGE
SCHEDULER
privilege. Window groups reside in the SYS
schema. Window groups, like windows, are created with access to PUBLIC
, therefore, no privileges are required to access window groups.
A window group cannot contain another window group.
This procedure defines a name or default value for a program argument that is of a complex type and must be encapsulated within an ANYDATA
object. A job that references the program can override the default value.
DBMS_SCHEDULER.DEFINE_ANYDATA_ARGUMENT ( program_name IN VARCHAR2, argument_position IN PLS_INTEGER, argument_name IN VARCHAR2 DEFAULT NULL, argument_type IN VARCHAR2, default_value IN SYS.ANYDATA, out_argument IN BOOLEAN DEFAULT FALSE);
Table 93-17 DEFINE_ANYDATA_ARGUMENT Procedure Parameters
Parameter | Description |
---|---|
|
The name of the program to be altered. A program with this name must exist. |
|
The position of the argument as it is passed to the executable. Argument numbers go from one to the |
|
The name to assign to the argument. It is optional, but must be unique for the program if it is specified. If you assign a name, the name can then be used by other package procedures, including the SET_JOB_ANYDATA_VALUE Procedures. |
|
The data type of the argument being defined. This is not verified or used by the Scheduler. It is only used by the user of the program when deciding what value to assign to the argument. |
|
The default value to be assigned to the argument encapsulated within an |
|
This parameter is reserved for future use. It must be set to |
All program arguments from 1 to the number_of_arguments
value must be defined before a program can be enabled. If a default value for an argument is not defined with this procedure, a value must be defined in the job.
Defining a program argument requires that you be the owner of the program or have ALTER
privileges on that program. You can also define a program argument if you have the CREATE
ANY
JOB
privilege.
This procedure adds or replaces a chain step and associates it with an event schedule or an inline event. Once started in a running chain, this step will not complete until the specified event has occurred. Every step in a chain must be defined before the chain can be enabled and used. Defining a step gives it a name and specifies what happens during the step. If a step already exists with this name, the new step will replace the old one.
DBMS_SCHEDULER.DEFINE_CHAIN_EVENT_STEP ( chain_name IN VARCHAR2, step_name IN VARCHAR2, event_schedule_name IN VARCHAR2, timeout IN INTERVAL DAY TO SECOND DEFAULT NULL); DBMS_SCHEDULER.DEFINE_CHAIN_EVENT_STEP ( chain_name IN VARCHAR2, step_name IN VARCHAR2, event_condition IN VARCHAR2, queue_spec IN VARCHAR2, timeout IN INTERVAL DAY TO SECOND DEFAULT NULL);
Table 93-18 DEFINE_CHAIN_EVENT_STEP Procedure Parameters
Parameter | Description |
---|---|
|
The name of the chain that the step is in |
|
The name of the step |
|
The name of the event schedule that the step waits for |
|
This parameter is reserved for future use |
|
See the CREATE_EVENT_SCHEDULE Procedure |
|
See the CREATE_EVENT_SCHEDULE Procedure |
Defining a chain step requires ALTER
privileges on the chain either by being the owner of the chain, or by having the ALTER
object privilege on the chain or by having the CREATE
ANY
JOB
system privilege.
See Also:
"DEFINE_CHAIN_STEP Procedure"This procedure adds a new rule to an existing chain, specified as a condition-action pair. The condition is expressed using either SQL or the Scheduler chain condition syntax, and indicates the prerequisites for the action to occur. The action specifies what is to be done as a result of the condition being met.
An actual rule object is created to store the rule in the schema in which the chain resides. If a rule name is given, this name will be used for the rule object. If a rule name is given and a rule already exists with this name in the chain's schema, the existing rule will be altered. (A schema different than the chain's schema cannot be specified). If no rule name is given, one will be generated of the form SCHED_RULE${N}
.
DBMS_SCHEDULER.DEFINE_CHAIN_RULE ( chain_name IN VARCHAR2, condition IN VARCHAR2, action IN VARCHAR2, rule_name IN VARCHAR2 DEFAULT NULL, comments IN VARCHAR2 DEFAULT NULL);
Table 93-19 DEFINE_CHAIN_RULE Procedure Parameters
Parameter | Description |
---|---|
|
The name of the chain to alter |
|
A boolean expression expressed using either SQL or the Scheduler chain condition syntax. (Scheduler chain condition syntax is described below.) The expression must evaluate to If the condition is expressed with SQL, it must use the syntax of a Every chain must have a rule that evaluates to |
|
The action to be performed when the rule evaluates to Possible actions include:
At the beginning of the |
|
The name of the rule that will be created. If no rule_name is given, one will be generated of the form |
|
An optional comment describing the rule. This will be stored in the rule object created. |
The Scheduler chain condition syntax provides an easy way to construct a condition using the states and error codes of steps in the current chain. The following are the available constructs, all of which are boolean expressions:
TRUE FALSE stepname [NOT] SUCCEEDED stepname [NOT] FAILED stepname [NOT] STOPPED stepname [NOT] COMPLETED stepname ERROR_CODE IN (integer, integer, integer ...) stepname ERROR_CODE NOT IN (integer, integer, integer ...) stepname ERROR_CODE = integer stepname ERROR_CODE != integer stepname ERROR_CODE <> integer stepname ERROR_CODE > integer stepname ERROR_CODE >= integer stepname ERROR_CODE < integer stepname ERROR_CODE <= integer
The following boolean operators are available to create more complex conditions:
expression AND expression expression OR expression NOT (expression)
integer
can be positive or negative. Parentheses may be used for clarity or to enforce ordering. You must use parentheses with the NOT
operator.
Defining a chain rule requires ALTER
privileges on the chain (either by being the owner, or by having ALTER
privileges on the chain or by having the CREATE
ANY
JOB
system privilege).
You must define at least one rule that starts the chain and at least one that ends it. See the section "Adding Rules to a Chain" in Oracle Database Administrator's Guide for more information.
The following are examples of using rule conditions and rule actions.
Rule Conditions Using Scheduler Chain Condition Syntax
'step1 completed' -- satisfied when step step1 has completed. (step1 completed is also TRUE when any -- of the following are TRUE: step1 succeeded, step1 failed, step1 stopped.) 'step1 succeeded and step2 succeeded' -- satisfied when steps step1 and step2 have both succeeded 'step1 error_code > 100' -- satisfied when step step1 has failed with an error_code greater than 100 'step1 error_code IN (1, 3, 5, 7)' -- satisfied when step step1 has failed with an error_code of 1, 3, 5, or 7
Rule Conditions Using SQL Syntax
':step1.completed = ''TRUE'' AND :step1.end_date >SYSDATE-1/24' --satisfied when step step1 completed less than an hour ago ':step1.duration > interval ''5'' minute' -- satisfied when step step1 has completed and took longer than 5 minutes to complete
'AFTER 01:00:00 START step1, step2' --After an hour start steps step1 and step2 'STOP step1' --Stop step step1 END step4.error_code' --End the chain with the error code that step step4 finished with. If step4 has not completed, the chain will be ended unsuccessfully with error code 27435. 'END' or 'END 0' --End the chain successfully (with error_code 0) 'END 100' --End the chain unsuccessfully with error code 100.
This procedure adds or replaces a chain step and associates it with a program or a nested chain. When the chain step is started, the specified program or chain is run. If a step already exists with the name supplied in the chain_name
argument, the new step replaces the old one.
The chain owner must have EXECUTE
privileges on the program or chain associated with the step. Only one program or chain can run during a step.
DBMS_SCHEDULER.DEFINE_CHAIN_STEP ( chain_name IN VARCHAR2, step_name IN VARCHAR2, program_name IN VARCHAR2);
Table 93-20 DEFINE_CHAIN_STEP Procedure Parameters
Parameter | Description |
---|---|
|
The name of the chain to alter. |
|
The name of the step being defined. If a step already exists with this name, the new step will replace the old one. |
|
The name of a program or chain to run during this step. The chain owner must have |
Defining a chain step requires ALTER
privileges on the chain (either by being the owner, or by having ALTER
privileges on the chain or by having the CREATE
ANY
JOB
system privilege).
See Also:
"DEFINE_CHAIN_EVENT_STEP Procedure"This procedure defines a special metadata argument for the program. The Scheduler can pass Scheduler metadata through this argument to your stored procedure or other executable. You cannot set values for jobs using this argument.
DBMS_SCHEDULER.DEFINE_METADATA_ARGUMENT ( program_name IN VARCHAR2, metadata_attribute IN VARCHAR2, argument_position IN PLS_INTEGER, argument_name IN VARCHAR2 DEFAULT NULL);
Table 93-21 DEFINE_METADATA_ARGUMENT Procedure Parameters
Parameter | Description |
---|---|
|
The name of the program to be altered |
|
The metadata to be passed. Valid metadata attributes are: ' Table Table 93-22 describes these attributes in detail. |
|
The position of the argument as it is passed to the executable. This cannot be greater than the |
|
The name to assign to the argument. It is optional, but must be unique for the program if it is specified. If you assign a name, the name can then be used by other package procedures. |
Table 93-22 Metadata Attributes
Metadata Attribute | Data Type | Description |
---|---|---|
|
|
Name of the currently running job |
|
|
Subname of the currently running job. The name + subname form a unique identifier for a job that is running a chain step. |
|
|
Owner of the currently running job |
|
|
When the currently running job started |
|
|
If the job was started by a window, the time that the window opened |
|
|
If the job was started by a window, the time that the window is scheduled to close |
|
(See Description) |
For an event-based job, the message content of the event that started the job. The data type of this attribute depends on the queue used for the event. It has the same type as the |
Defining a program argument requires that you be the owner of the program or have ALTER
privileges on that program. You can also define a program argument if you have the CREATE
ANY
JOB
privilege.
All metadata attributes except event_message
can also be used in PL/SQL blocks that you enter into the job_action
or program_action
attributes of jobs or programs, respectively. You use the attribute name as you use any other PL/SQL identifier, and the Scheduler assigns it a value.
This procedure defines a name or default value for a program argument. If no default value is defined for a program argument, the job that references the program must supply an argument value. (The job can also override a default value.)
This procedure is overloaded.
Defines a program argument without a default value:
PROCEDURE define_program_argument( program_name IN VARCHAR2, argument_position IN PLS_INTEGER, argument_name IN VARCHAR2 DEFAULT NULL, argument_type IN VARCHAR2, out_argument IN BOOLEAN DEFAULT FALSE);
Defines a program argument with a default value:
PROCEDURE define_program_argument( program_name IN VARCHAR2, argument_position IN PLS_INTEGER, argument_name IN VARCHAR2 DEFAULT NULL, argument_type IN VARCHAR2, default_value IN VARCHAR2, out_argument IN BOOLEAN DEFAULT FALSE);
Table 93-23 DEFINE_PROGRAM_ARGUMENT Procedure Parameters
Parameter | Description |
---|---|
|
The name of the program to be altered. A program with this name must exist. |
|
The position of the argument as it is passed to the executable. Argument numbers go from one to the |
|
The name to assign to the argument. It is optional, but must be unique for the program if it is specified. If you assign a name, the name can then be used by other package procedures, including the SET_JOB_ARGUMENT_VALUE Procedures. |
|
The data type of the argument being defined. This is not verified or used by the Scheduler. It is only used by the user of the program when deciding what value to assign to the argument. |
|
The default value to be assigned to the argument if none is specified by the job. |
|
This parameter is reserved for future use. It must be set to |
All program arguments from 1 to the number_of_arguments
value must be defined before a program can be enabled. If a default value for an argument is not defined with this procedure, a value must be defined in the job.
Defining a program argument requires that you be the owner of the program or have ALTER
privileges on that program. You can also define a program argument if you have the CREATE
ANY
JOB
privilege.
This procedure disables a program, job, chain, window, or window group.
DBMS_SCHEDULER.DISABLE ( name IN VARCHAR2, force IN BOOLEAN DEFAULT FALSE);
Table 93-24 DISABLE Procedure Parameters
Parameter | Description |
---|---|
|
The name of the object being disabled. Can be a comma-delimited list. If a job class name is specified, then all the jobs in the job class are disabled. The job class is not disabled. If a window group name is specified, then the window group will be disabled, but the windows that are members of the window group will not be disabled. |
|
Whether to ignore dependencies. See the usage notes for more information. |
Disabling an object that is already disabled does not generate an error. Because the DISABLE
procedure is used for several Scheduler objects, when disabling windows and window groups, they must be preceded by SYS
.
The purpose of the force
option is to point out dependencies. No dependent objects are altered.
To run DISABLE
for a window or window group, you must have the MANAGE
SCHEDULER
privilege. Otherwise, you must be the owner of the object being disabled or have ALTER
privileges on that object or have the CREATE
ANY
JOB
privilege.
Disabling a job means that, although the metadata of the job is there, it should not run and the job coordinator will not pick up these jobs for processing. When a job is disabled, its state
in the job queue is changed to disabled
.
If force
is set to FALSE
and the job is currently running, an error is returned.
If force
is set to TRUE
, the job is disabled, but the currently running instance is allowed to finish.
When a program is disabled, the status is changed to disabled. A disabled program implies that, although the metadata is still there, jobs that point to this program cannot run.
If force
is set to FALSE
, the program must be unreferenced by any job otherwise an error will occur.
If force
is set to TRUE
, those jobs that point to the program will not be disabled, however, they will fail at runtime because their program will not be valid.
Running jobs that point to the program are not affected by the DISABLE
call, and are allowed to continue
Any argument that pertains to the program will not be affected when the program is disabled.
This means that the window will not open, however, the metadata of the window is still there, so it can be reenabled.
If force
is set to FALSE
, the window must not be open or referenced by any job otherwise an error will occur.
If force
is set to TRUE
, disabling a window that is open will succeed but the window will not be closed. It will prevent the window from opening in the future until it is re-enabled.
When the window is disabled, those jobs that have the window as their schedule will not be disabled.
When a window group is disabled, jobs, other than a running job, that has the window group as its schedule will not run even if the member windows open. However, if the job had one of the window group members as its schedule, it would still run.
The metadata of the window group is still there, so it can be reenabled. Note that the members of the window group will still open.
If force
is set to FALSE
, the window group must not have any members that are open or referenced by any job otherwise an error will occur.
If force
is set to TRUE
:
The window group is disabled and the open window will be not closed or disabled. It will be allowed to continue to its end.
The window group is disabled but those jobs that have the window group as their schedule will not be disabled.
When a chain is disabled, the metadata for the chain is still there, but jobs that point to it will not be able to be run. This allows changes to the chain to be made safely without the risk of having an incompletely specified chain run.If force
is set to FALSE
, the chain must be unreferenced by any job, otherwise an error will occur.If force
is set to TRUE
, those jobs that point to the chain will not be disabled, however, they will fail at runtime.Running jobs that point to this chain are not affected by the DISABLE
call and are allowed to complete.
This procedure drops an existing chain.
DBMS_SCHEDULER.DROP_CHAIN ( chain_name IN VARCHAR2, force IN BOOLEAN DEFAULT FALSE);
Table 93-25 DROP_CHAIN Procedure Parameters
Parameter | Description |
---|---|
|
The name of a chain to drop. Can also be a comma-delimited list of chains. |
|
If If |
Dropping a chain requires alter privileges on the chain (either by being the owner, or by having ALTER
privileges on the chain or by having the CREATE
ANY
JOB
system privilege).
All steps associated with the chain are dropped. If no rule set was specified when the chain was created, then the automatically created rule set and evaluation context associated with the chain are also dropped, so the user needs to have the privileges required to do this. See the DBMS_RULE_ADM.DROP_RULE_SET
and DBMS_RULE_ADM.DROP_EVALUATION_CONTEXT
procedures for more information.
If force
is FALSE
, no jobs must be using this chain. If force
is TRUE
, any jobs that use this chain will be disabled before dropping the chain (and any of these jobs that are running will be stopped).
This procedure removes a rule from an existing chain. The rule object corresponding to this rule will also be dropped. The chain will not be disabled. If dropping this rule makes the chain invalid, the user should first disable the chain to ensure that it does not run.
DBMS_SCHEDULER.DROP_CHAIN_RULE ( chain_name IN VARCHAR2, rule_name IN VARCHAR2, force IN BOOLEAN DEFAULT FALSE);
Table 93-26 DROP_CHAIN_RULE Procedure Parameters
Parameter | Description |
---|---|
|
The name of the chain to alter |
|
The name of the rule to drop |
|
If |
Dropping a chain rule requires alter privileges on the chain (either by being the owner, or by having ALTER
privileges on the chain or by having the CREATE
ANY
JOB
system privilege).
Dropping a chain rule also drops the underlying rule database object so the user needs to have the privileges to drop this rule object. See the DBMS_RULE_ADM.DROP_RULE
procedure for more information.
This procedure drops a chain step. If this chain step is still used in the chain rules, the chain will be disabled.
DBMS_SCHEDULER.DROP_CHAIN_STEP ( chain_name IN VARCHAR2, step_name IN VARCHAR2, force IN BOOLEAN DEFAULT FALSE);
Table 93-27 DROP_CHAIN_STEP Procedure Parameters
Parameter | Description |
---|---|
|
The name of the chain to alter |
|
The name of the step being dropped. Can be a comma-separated list. |
|
If |
Dropping a chain step requires ALTER
privileges on the chain (either by being the owner, or by having ALTER
privileges on the chain or by having the CREATE
ANY
JOB
system privilege).
This procedure drops a job or all jobs in a job class. It results in the job being removed from the job queue, its metadata being removed, and no longer being visible in the *_SCHEDULER_JOBS
views. Therefore, no more runs of the job will be executed. Dropping a job also drops all argument values set for that job.
DBMS_SCHEDULER.DROP_JOB ( job_name IN VARCHAR2, force IN BOOLEAN DEFAULT FALSE);
Table 93-28 DROP_JOB Procedure Parameters
Parameter | Description |
---|---|
|
The name of a job or job class. Can be a comma-delimited list. For a job class, the If the name of a job class is specified, the jobs that belong to that job class are dropped, but the job class itself is not dropped. |
|
If If |
Dropping a job requires ALTER
privileges on the job either by being the owner of the job, or by having the ALTER
object privilege on the job or by having the CREATE
ANY
JOB
system privilege.
This procedure drops a job class. Dropping a job class means that all the metadata about the job class is removed from the database.
DBMS_SCHEDULER.DROP_JOB_CLASS ( job_class_name IN VARCHAR2, force IN BOOLEAN DEFAULT FALSE);
Table 93-29 DROP_JOB_CLASS Procedure Parameters
Parameter | Description |
---|---|
|
The name of the job class. Can be a comma-delimited list. |
|
If If Running jobs that belong to the job class are not affected. |
Dropping a job class requires the MANAGE
SCHEDULER
system privilege.
This procedure drops a program. Any arguments that pertain to the program are also dropped when the program is dropped.
DBMS_SCHEDULER.DROP_PROGRAM ( program_name IN VARCHAR2, force IN BOOLEAN DEFAULT FALSE);
Table 93-30 DROP_PROGRAM Procedure Parameters
Parameter | Description |
---|---|
|
The name of the program to be dropped. Can be a comma-delimited list. |
|
If If Running jobs that point to the program are not affected by the |
Dropping a program requires that you be the owner of the program or have ALTER
privileges on that program. You can also drop a program if you have the CREATE
ANY
JOB
privilege.
This procedure drops a program argument. An argument can be specified by either name (if one has been given) or position.
The procedure is overloaded.
Drops a program argument by position:
DBMS_SCHEDULER.DROP_PROGRAM_ARGUMENT ( program_name IN VARCHAR2, argument_position IN PLS_INTEGER);
Drops a program argument by name:
DBMS_SCHEDULER.DROP_PROGRAM_ARGUMENT ( program_name IN VARCHAR2, argument_name IN VARCHAR2);
Table 93-31 DROP_PROGRAM_ARGUMENT Procedure Parameters
Parameter | Description |
---|---|
|
The name of the program to be altered. A program with this name must exist. |
|
The name of the argument being dropped |
|
The position of the argument to be dropped |
Dropping a program argument requires that you be the owner of the program or have ALTER
privileges on that program. You can also drop a program argument if you have the CREATE
ANY
JOB
privilege.
This procedure drops a schedule.
DBMS_SCHEDULER.DROP_SCHEDULE ( schedule_name IN VARCHAR2, force IN BOOLEAN DEFAULT FALSE);
Table 93-32 DROP_SCHEDULE Procedure Parameters
Parameter | Description |
---|---|
|
The name of the schedule. Can be a comma-delimited list. |
|
If If Running jobs and open windows that point to the schedule are not affected. |
You must be the owner of the schedule being dropped or have ALTER
privileges for the schedule or the CREATE
ANY
JOB
privilege.
This procedure drops a window. All metadata about the window is removed from the database. All references to the window are removed from window groups.
DBMS_SCHEDULER.DROP_WINDOW ( window_name IN VARCHAR2, force IN BOOLEAN DEFAULT FALSE);
Table 93-33 DROP_WINDOW Procedure Parameters
Parameter | Description |
---|---|
|
The name of the window. Can be a comma-delimited list. |
|
If If Running jobs that have the window as their schedule will be allowed to continue, unless the |
Dropping a window requires the MANAGE
SCHEDULER
privilege.
This procedure drops a window group but not the windows that are members of this window group.
DBMS_SCHEDULER.DROP_WINDOW_GROUP ( group_name IN VARCHAR2 force IN BOOLEAN DEFAULT FALSE);
Table 93-34 DROP_WINDOW_GROUP Procedure Parameters
Parameter | Description |
---|---|
|
The name of the window group |
|
If If If a member of the window group that is being dropped is open, the window group can still be dropped. |
If you want to drop all the windows that are members of this group but not the window group itself, you can use the DROP_WINDOW
procedure and provide the name of the window group to the call.
To drop a window group, you must have the MANAGE
SCHEDULER
privilege.
This procedure enables a program, job, chain, window, or window group. When an object is enabled, the enabled flag is set to TRUE
. By default, jobs, chains, and programs are created disabled and windows and window groups are created enabled.
Validity checks are performed before enabling an object. If the check fails, the object is not enabled, and an appropriate error is returned. This procedure does not return an error if the object was already enabled.
DBMS_SCHEDULER.ENABLE ( name IN VARCHAR2);
Table 93-35 ENABLE Procedure Parameters
Parameter | Description |
---|---|
|
The name of the Scheduler object being enabled. Can be a comma-delimited list of names. If a job class name is specified, then all the jobs in the job class are enabled. If a window group name is specified, then the window group will be enabled, but the windows that are members of the window group, will not be enabled. |
Because the ENABLE
procedure is used for several Scheduler objects, when enabling windows or window groups, they must be preceded by SYS
.
To run ENABLE
for a window or window group, you must have the MANAGE
SCHEDULER
privilege. Otherwise, you must be the owner of the object being enabled or have ALTER
privileges on that object or have the CREATE
ANY
JOB
privilege. For a job of type EXECUTABLE
(or for a job that points to a program of type EXECUTABLE
), the job owner must have the CREATE EXTERNAL JOB
system privilege before the job can be enabled or run.
You can define repeat intervals of jobs, windows or schedules using the Scheduler's calendaring syntax. This procedure evaluates the calendar expression and tells you what the next execution date of a job or window will be. This is very useful for testing the correct definition of the calendar string without having to actually schedule the job or window.
This procedure can also be used to get multiple steps of the repeat interval by passing the next_run_date
returned by one invocation as the return_date_after
argument of the next invocation of this procedure.
DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING ( calendar_string IN VARCHAR2, start_date IN TIMESTAMP WITH TIME ZONE, return_date_after IN TIMESTAMP WITH TIME ZONE, next_run_date OUT TIMESTAMP WITH TIME ZONE);
Table 93-36 EVALUATE_CALENDAR_STRING Procedure Parameters
Parameter | Description |
---|---|
|
The calendar string to be evaluated. The string must be in the calendaring syntax described in "Operational Notes". |
|
The date after which the repeat interval becomes valid. It can also be used to fill in specific items that are missing from the calendar string. Can optionally be |
|
With the |
|
The first timestamp that matches the calendar string and start date that occurs after the value passed in for the |
The following code fragment can be used to determine the next five dates a job will run given a specific calendar string.
SET SERVEROUTPUT ON; ALTER SESSION set NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS'; Session altered. DECLARE start_date TIMESTAMP; return_date_after TIMESTAMP; next_run_date TIMESTAMP; BEGIN start_date := to_timestamp_tz('01-JAN-2003 10:00:00','DD-MON-YYYY HH24:MI:SS'); return_date_after := start_date; FOR i IN 1..5 LOOP DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING( 'FREQ=DAILY;BYHOUR=9;BYMINUTE=30;BYDAY=MON,TUE,WED,THU,FRI', start_date, return_date_after, next_run_date); DBMS_OUTPUT.PUT_LINE('next_run_date: ' || next_run_date); return_date_after := next_run_date; END LOOP; END; / next_run_date: 02-JAN-03 09.30.00.000000 AM next_run_date: 03-JAN-03 09.30.00.000000 AM next_run_date: 06-JAN-03 09.30.00.000000 AM next_run_date: 07-JAN-03 09.30.00.000000 AM next_run_date: 08-JAN-03 09.30.00.000000 AM PL/SQL procedure successfully completed.
No specific Scheduler privileges are required.
This procedure forces reevaluation of the rules of a running chain to trigger any rules for which the conditions have been satisfied. The job passed as an argument must point to a chain and must be running. If the job is not running, an error is thrown. (RUN_JOB
can be used to start the job.)
If any of the steps of the chain are themselves running chains, another EVALUATE_RUNNING_CHAIN
is performed on each of the nested running chains.
DBMS_SCHEDULER.EVALUATE_RUNNING_CHAIN ( job_name IN VARCHAR2);
Table 93-37 EVALUATE_RUNNING_CHAIN Procedure Parameter
Parameter | Description |
---|---|
|
The name of the running job (pointing to a chain) to reevaluate the rules for |
Running EVALUATE_RUNNING_CHAIN
on a job requires alter privileges on the job (either by being the owner, or by having ALTER
privileges on the job or by having the CREATE
ANY
JOB
system privilege).
Note:
The Scheduler automatically evaluates a chain:At the start of the chain job
When a chain step completes
When an event occurs that is associated with one of the event steps of the chain
For most chains, this is sufficient. EVALUATE_RUNNING_CHAIN
should be used only under the following circumstances:
After manual intervention of a running chain with the ALTER_RUNNING_CHAIN
procedure
When chain rules use SQL syntax and the rule conditions contain elements that are not under the control of the Scheduler.
In these cases, EVALUATE_RUNNING_CHAIN
may not be needed if you set the evaluation_interval
attribute when you created the chain.
This function returns a unique name for a job. The name will be of the form {prefix}N
where N
is a number from a sequence. If no prefix is specified, the generated name will, by default, be JOB$_1
, JOB$_2
, JOB$_3
, and so on. If 'SCOTT'
is specified as the prefix, the name will be SCOTT1
, SCOTT2
, and so on.
DBMS_SCHEDULER.GENERATE_JOB_NAME ( prefix IN VARCHAR2 DEFAULT 'JOB$_') RETURN VARCHAR2;
Table 93-38 GENERATE_JOB_NAME Function Parameter
Parameter | Description |
---|---|
|
The prefix to use when generating the job name |
If the prefix is explicitly set to NULL
, the name will be just the sequence number. In order to successfully use such numeric names, they must be surrounded by double quotes throughout the DBMS_SCHEDULER
calls. A prefix cannot be longer than 18 characters and cannot end with a digit.
Note that, even though the GENERATE_JOB_NAME
function will never return the same job name twice, there is a small chance that the returned name matches an already existing database object.
No specific Scheduler privileges are required to use this function.
This procedure retrieves the value of an attribute of a Scheduler object. It is overloaded to output values of the following types: VARCHAR2
, TIMESTAMP
WITH
TIMEZONE
, BOOLEAN
, PLS_INTEGER
, and INTERVAL
DAY
TO
SECOND
.
DBMS_SCHEDULER.GET_ATTRIBUTE ( name IN VARCHAR2, attribute IN VARCHAR2, value OUT [VARCHAR2, TIMESTAMP WITH TIMEZONE, PLS_INTEGER, BOOLEAN, INTERVAL DAY TO SECOND); DBMS_SCHEDULER.GET_ATTRIBUTE ( name IN VARCHAR2, attribute IN VARCHAR2, value OUT [VARCHAR2, TIMESTAMP WITH TIMEZONE, PLS_INTEGER, BOOLEAN, INTERVAL DAY TO SECOND], value2 OUT VARCHAR2);
Table 93-39 GET_ATTRIBUTE Procedure Parameters
Parameter | Description |
---|---|
|
The name of the object |
|
The attribute being retrieved |
|
The existing value of the attribute |
|
Most attributes have only one value associated with them, but some can have two. The |
To run GET_ATTRIBUTE
for a job class, you must have the MANAGE
SCHEDULER
privilege or have EXECUTE
privileges on the class. For a schedule, window, or a window group, no privileges are necessary. Otherwise, you must be the owner of the object or have ALTER
or EXECUTE
privileges on that object or have the CREATE ANY JOB
privilege.
This procedure retrieves the value of a Scheduler attribute.
DBMS_SCHEDULER.GET_SCHEDULER_ATTRIBUTE ( attribute IN VARCHAR2, value OUT VARCHAR2);
Table 93-40 GET_SCHEDULER_ATTRIBUTE Procedure Parameters
Parameter | Description |
---|---|
|
The name of the attribute |
|
The existing value of the attribute |
To run GET_SCHEDULER_ATTRIBUTE
, you must have the MANAGE
SCHEDULER
privilege.
Table 93-41 lists the Scheduler attributes that you can retrieve. For more detail on these attributes, see Table 93-61 and the section "Configuring the Scheduler" in Oracle Database Administrator's Guide.
Table 93-41 Scheduler Attributes Retrievable with GET_SCHEDULER_ATTRIBUTE
Scheduler Attribute | Description |
---|---|
|
Default time zone used by the Scheduler for repeat intervals and windows |
|
Retention period in days for job and window logs |
|
Maximum number of job slave processes that the Scheduler can start. May be |
|
Name of the currently open window |
|
Time in seconds before an event generated by the Scheduler and enqueued onto the Scheduler event queue expires. May be |
This procedure opens a window independent of its schedule. This window will open and the resource plan associated with it, will take effect immediately for the duration specified or for the normal duration of the window if no duration is given. Only an enabled window can be manually opened.
DBMS_SCHEDULER.OPEN_WINDOW ( window_name IN VARCHAR2, duration IN INTERVAL DAY TO SECOND, force IN BOOLEAN DEFAULT FALSE);
Table 93-42 OPEN_WINDOW Procedure Parameters
Parameter | Description |
---|---|
|
The name of the window |
|
The duration of the window. It is of type interval day to second. If it is |
|
If If You can open a window that is already open. The window stays open for the duration specified in the call, from the time the The Scheduler automatically closes any window that is open at that time, even if it has a higher priority. For the duration of this manually opened window, the Scheduler does not open any other scheduled windows even if they have a higher priority. |
If there are jobs running when the window opens, the resources allocated to them might change due to the switch in resource plan.
Opening a window manually has no impact on regular scheduled runs of the window. The next open time of the window is not updated, and will be as determined by the regular scheduled opening.
When a window that was manually opened closes, the rules about overlapping windows are applied to determine which other window should be opened at that time if any at all.
If a window fails to switch resource plans because the designated resource plan no longer exists or because resource plan switching by windows is disabled (for example, by using the ALTER
SYSTEM
statement with the force
option), the failure to switch resource plans is recorded in the window log.
Opening a window requires the MANAGE
SCHEDULER
privilege.
By default, the Scheduler automatically purges all rows in the job log and window log that are older than 30 days. The PURGE_LOG
procedure is used to purge additional rows from the job and window log.
Rows in the job log table pertaining to the steps of a chain are purged only when the entry for the main chain job is purged (either manually or automatically).
DBMS_SCHEDULER.PURGE_LOG ( log_history IN PLS_INTEGER DEFAULT 0, which_log IN VARCHAR2 DEFAULT 'JOB_AND_WINDOW_LOG', job_name IN VARCHAR2 DEFAULT NULL);
Table 93-43 PURGE_LOG Procedure Parameters
Parameter | Description |
---|---|
|
This specifies how much history (in days) to keep. The valid range is 0 - 999. If set to 0, no history is kept. |
|
This specifies which type of log. Valid values for |
|
This specifies which job-specific entries must be purged from the jog log. This can be a comma-delimited list of job names and job classes. Whenever |
This procedure requires the MANAGE
SCHEDULER
privilege.
The following will completely purge all rows from both the job log and the window log:
DBMS_SCHEDULER.PURGE_LOG();
The following will purge all rows from the window log that are older than 5 days:
DBMS_SCHEDULER.PURGE_LOG(5, 'window_log');
The following will purge all rows from the window log that are older than 1 day and all rows from the job log that are related to jobs in jobclass1
and that are older than 1 day:
DBMS_SCHEDULER.PURGE_LOG(1, 'job_and_window_log', 'sys.jobclass1');
This procedure unsubscribes a user from the Scheduler event queue SYS.SCHEDULER$_EVENT_QUEUE
.
DBMS_SCHEDULER.REMOVE_EVENT_QUEUE_SUBSCRIBER ( subscriber_name IN VARCHAR2 DEFAULT NULL);
Table 93-44 REMOVE_EVENT_QUEUE_SUBSCRIBER Procedure Parameters
Parameter | Description |
---|---|
|
Name of the Oracle Streams Advanced Queuing (AQ) agent for which to remove the subscription. If |
After the agent is unsubscribed, it is deleted. If the agent does not exist or is not currently subscribed to the Scheduler event queue, an error is raised.
This procedure removes one or more windows from an existing window group.
DBMS_SCHEDULER.REMOVE_WINDOW_GROUP_MEMBER ( group_name IN VARCHAR2, window_list IN VARCHAR2);
Table 93-45 REMOVE_WINDOW_GROUP_MEMBER Procedure Parameters
Parameter | Description |
---|---|
|
The name of the window group. |
|
The name of the window or windows. |
If any of the windows specified is either invalid, does not exist, or is not a member of the given group, the call fails. Removing a window from a group requires the MANAGE
SCHEDULER
privilege.
Dropping an open window from a window group has no impact on running jobs that has the window as its schedule since the jobs would only be stopped when a window closes.
This procedure resets (clears) the value previously set to an argument for a job.
RESET_JOB_ARGUMENT_VALUE
is overloaded.
Clears a previously set job argument value by argument position:
DBMS_SCHEDULER.RESET_JOB_ARGUMENT_VALUE ( job_name IN VARCHAR2, argument_position IN PLS_INTEGER);
Clears a previously set job argument value by argument name:
DBMS_SCHEDULER.RESET_JOB_ARGUMENT_VALUE ( job_name IN VARCHAR2, argument_name IN VARCHAR2);
Table 93-46 RESET_JOB_ARGUMENT_VALUE Procedure Parameters
Parameter | Description |
---|---|
|
The name of the job being altered |
|
The position of the program argument being reset |
|
The name of the program argument being reset |
If the corresponding program argument has no default value, the job will be disabled. Resetting a program argument of a job belonging to another user requires ALTER
privileges on that job. Arguments can be specified by position or by name.
RESET_JOB_ARGUMENT_VALUE
requires that you be the owner of the job or have ALTER
privileges on that job. You can also reset a job argument value if you have the CREATE
ANY
JOB
privilege.
This procedure immediately runs a chain or part of a chain by creating a run-once job with the job name given. If no job_name
is given, one will be generated of the form RUN_CHAIN$_
chainname
N
, where chainname
is the first 8 characters of the chain name and N is an integer.If a list of start steps is given, only those steps will be started when the chain begins running. Steps not in the list that would normally have started are skipped and paused (so that they or the steps after them do not run). If start_steps
is NULL
, then the chain will start normally—that is, an initial evaluation will be done to see which steps to start running).
If a list of initial step states is given, the newly created chain job sets every listed step to the state specified for that step before evaluating the chain rules to see which steps to start. (Steps in the list are not started.)
Runs a chain, with a list of start steps.
DBMS_SCHEDULER.RUN_CHAIN ( chain_name IN VARCHAR2, start_steps IN VARCHAR2, job_name IN VARCHAR2 DEFAULT NULL);
Runs a chain, with a list of initial step states.
DBMS_SCHEDULER.RUN_CHAIN ( chain_name IN VARCHAR2, step_state_list IN SYS.SCHEDULER$_STEP_TYPE_LIST, job_name IN VARCHAR2 DEFAULT NULL);
Table 93-47 RUN_CHAIN Procedure Parameters
Parameter | Description |
---|---|
|
The name of the chain to run |
|
The name of the job to create to run the chain |
|
Comma-separated list of the steps to start when the chain starts running |
|
List of chain steps with an initial state ( The list is provided as nested table type CREATE TYPE sys.scheduler$_step_type as object ( step_name varchar2(32), step_type varchar2(32)); CREATE TYPE sys.scheduler$_step_type_list IS TABLE OF sys.scheduler$_step_type; Set the attributes of
where error_number is a positive or negative integer. |
Running a chain requires CREATE
JOB
if the job is being created in the user's schema, or CREATE
ANY
JOB
otherwise. In addition, the owner of the job being created needs execute privileges on the chain (by being the owner of the chain, by having the EXECUTE
privilege on the chain, or by having the EXECUTE
ANY
PROGRAM
system privilege).
The following example illustrates how to start a chain in the middle by providing the initial state of some chain steps.
declare initial_step_states sys.scheduler$_step_type_list; begin initial_step_states := sys.scheduler$_step_type_list( sys.scheduler$_step_type('step1', 'SUCCEEDED'), sys.scheduler$_step_type('step2', 'FAILED 27486'), sys.scheduler$_step_type('step3', 'SUCCEEDED'), sys.scheduler$_step_type('step5', 'SUCCEEDED')); dbms_scheduler.run_chain('my_chain', initial_step_states); end; /
This procedure runs a job immediately.
DBMS_SCHEDULER.RUN_JOB ( job_name IN VARCHAR2, use_current_session IN BOOLEAN DEFAULT TRUE);
Table 93-48 RUN_JOB Procedure Parameters
Parameter | Description |
---|---|
|
The name of the job being run |
|
This specifies whether the job run should occur in the same session as the one that the procedure was invoked from. When
When
|
The job does not have to be enabled. If the job is disabled, the following validity checks are performed before running it:
The job points to a valid job class.The job owner has EXECUTE
privileges on the job class.If a program or chain is referenced, the program/chain exists.If a program or chain is referenced, the job owner has privileges to execute the program/chain.All argument values have been set (or have defaults).The job owner has the CREATE EXTERNAL JOB
privilege if this is an external job.
The job can be run in two different modes. One is in the current user session. In this case, the call to RUN_JOB
will block until it has completed the job. Any errors that occur during the execution of the job will be returned as errors to the RUN_JOB
procedure. The other option is to run the job immediately like a regular job. In this case, RUN_JOB
returns immediately and the job will be picked up by the coordinator and passed on to a job slave for execution. The Scheduler views and logs must be queried for the outcome of the job.
Multiple user sessions can use RUN_JOB
in their sessions simultaneously when use_current_session
is set to TRUE
.
When using RUN_JOB
with jobs that point to chains, use_current_session
must be FALSE
.
RUN_JOB
requires that you be the owner of the job or have ALTER
privileges on that job. You can also run a job if you have the CREATE
ANY
JOB
privilege.
This procedure changes an attribute of an object. It is overloaded to accept values of the following types: VARCHAR2
, TIMESTAMP
WITH
TIMEZONE
, BOOLEAN
, PLS_INTEGER
, and INTERVAL
DAY
TO
SECOND
. To set an attribute to NULL
, the SET_ATTRIBUTE_NULL
procedure should be used. What attributes can be set depends on the object being altered. With the exception of the object name, all object attributes can be changed.
SET_ATTRIBUTE
is overloaded.
DBMS_SCHEDULER.SET_ATTRIBUTE ( name IN VARCHAR2, attribute IN VARCHAR2, value IN [VARCHAR2, TIMESTAMP WITH TIMEZONE, PLS_INTEGER, BOOLEAN, INTERVAL DAY TO SECOND]); DBMS_SCHEDULER.SET_ATTRIBUTE ( name IN VARCHAR2, attribute IN VARCHAR2, value IN [VARCHAR2, TIMESTAMP WITH TIMEZONE, PLS_INTEGER, BOOLEAN, INTERVAL DAY TO SECOND], value2 IN VARCHAR2 DEFAULT NULL);
Table 93-49 SET_ATTRIBUTE Procedure Parameters
Parameter | Description |
---|---|
|
The name of the object |
|
See Table 93-50 through Table 93-57. |
|
The new value being set for the attribute. This cannot be |
|
Most attributes have only one value associated with them, but some can have two. The |
If an object is altered and it was in the enabled state, the Scheduler will first disable it, make the change and then re-enable it. If any errors are encountered during the enable process, the object is not re-enabled and an error is generated.
If an object is altered and it was in the disabled state, it will remain disabled after it is altered.
To run SET_ATTRIBUTE
for a window, window group, or job class, you must have the MANAGE
SCHEDULER
privilege. Otherwise, you must be the owner of the object being altered or have ALTER
privileges on that object or have the CREATE
ANY
JOB
privilege.
If there is a running instance of the job when the SET_ATTRIBUTE
call is made, it is not affected by the call. The change is only seen in future runs of the job.
If any of the schedule attributes of a job are altered while the job is running, the time of the next job run will be scheduled using the new schedule attributes. Schedule attributes of a job include schedule_name
, start_date
, end_date
, and repeat_interval
.
If any of the program attributes of a job are altered while the job is running, the new program attributes will take effect the next time the job runs. Program attributes of a job include program_name
, job_action
, job_type
, and number_of_arguments
. This is also the case for job argument values that have been set.
Granting ALTER
on a job will let a user alter all attributes of that job except its program attributes (program_name
, job_type
, job_action
, program_action
, and number_of_arguments
) and will not allow a user to use a PL/SQL expression to specify the schedule for a job.
We recommend you not to alter a job that was automatically created for you by the database. Jobs that were created by the database have the column SYSTEM
set to TRUE
in job views.
If any currently running jobs use the program that is altered, they will continue to run with the program definition prior to the alter. The job will run with the new program definition the next time the job executes.
If a schedule is altered, the change will not affect running jobs and open windows that use this schedule. The change will only be in effect the next time the jobs runs or the window opens.
With the exception of the default job class, all job classes can be altered. To alter a job class, you must have the MANAGE
SCHEDULER
privilege.
When a job class is altered, running jobs that belong to the class are not affected. The change only takes effect for jobs that have not started running yet.
When a window is altered, it does not affect an active window. The changes only take effect the next time the window opens.
To change resource plans, you must first set the RESOURCE_MANAGER_PLAN
initialization parameter in the init.ora
file or issue an ALTER SYSTEM SET RESOURCE_MANAGER_PLAN =
my_plan
statement before the window opens.
Table 93-50 lists job attribute values.
Note:
See theCREATE_JOB
procedure for more complete descriptions of the attributes in this table.Table 93-50 Job Attribute Values
Name | Description |
---|---|
|
This attribute specifies how much information is logged. The three possible options are:
No logging will be performed for any jobs in this class.
The Scheduler will write detailed information to the job log for all runs of each job in this class.
In addition to recording every run of a job, the Scheduler will record all operations performed on all jobs in this class. In other words, every time a job is created, enabled, disabled, altered, and so on will be recorded in the log. |
|
This attribute specifies whether a job can be restarted in case of failure. By default, jobs are not restartable and this attribute is set to In the case of a chain job, if this attribute is Note that setting this attribute to Retries on errors are not counted as regular runs. The run count or failure count is not incremented until the job succeeds or has failed all its six retries. The restartable attribute is used by the Scheduler to determine whether to retry the job not only on regular application errors, but after a database malfunction as well. The Scheduler will retry the job a maximum of six times. The first time, it will wait for one second and multiply this wait time with a factor of 10 each time thereafter. Both the run count and failure count are incremented by 1 if the job has failed all its six retries. If the job immediately succeeds, or it succeeds on one of its retries, run count is incremented by 1. The Scheduler will stop retrying a job when:
The Scheduler no longer retries the job if the next scheduled retry is past the next regularly scheduled run for repeating jobs. |
|
This attribute specifies the number of times a job can fail on consecutive scheduled runs before it is automatically disabled. Once a job is disabled, it is no longer executed and its
|
|
This attribute specifies the maximum number of consecutive scheduled runs of the job. Once
|
|
This attribute specifies the maximum amount of time that the job should be allowed to run. Its datatype is |
|
This attribute is for expert users of parallel technology only. If your job will be using parallel technology, you can set the value of this attribute to the degree of parallelism of your SQL inside the job.
|
|
This attribute should only be used for a database running in RAC mode. By default, it is set to If For non-RAC environments, this attribute is not useful because there is only one instance. |
|
This attribute only applies if the schedule of a job is a window or a window group. Setting this attribute to By default, Note that, although the job is allowed to continue, its resource allocation will probably change because closing a window generally also implies a change in resource plans. |
|
This attribute specifies the priority of this job relative to other jobs in the same class as this job. If multiple jobs within a class are scheduled to be executed at the same time, the job priority determines the order in which jobs from that class are picked up for execution by the job coordinator. It can be a value from 1 through 5, with 1 being the first to be picked up for job execution. If no job priority is specified when creating a job, the default priority of 3 is assigned to it. |
|
In heavily loaded systems, jobs are not always started at their scheduled time. This attribute enables you to have the Scheduler not start a job at all if the delay in starting the job is larger than the interval specified. It can be a value of 1 minute to 99 days. For example, if a job was supposed to start at noon and the schedule limit is set to 60 minutes, the job will not be run if it has not started to run by 1:00 p.m. If |
|
The name of a program object to use with this job. If this is set, |
|
The action that the job performs, depending on the |
|
The type of this job. Can be any of: ' If this is set, |
|
The number of arguments if the program is inlined. If this is set, |
|
The name of a schedule or window or window group to use as the schedule for this job. If this is set, |
|
Either a PL/SQL function returning the next date on which to run, or calendaring syntax expression. If this is set, |
|
The original date on which this job started or will be scheduled to start. If this is set, |
|
The date after which the job will no longer run. It will be dropped if |
|
The class this job is associated with. |
|
An optional comment. |
|
Whether the job should be dropped after having completed. |
|
This attribute takes two values: the |
|
This attribute tells the Scheduler at what stages of the job's execution events should be raised. It is a bit vector in which zero or more of the following bits can be set. Each bit has a package constant corresponding to it.
Table Table 93-51 describes these event types in detail. |
Table 93-51 Event Types Raised by the Scheduler
Event Type | Description |
---|---|
|
The job started |
|
The job completed successfully |
|
The job failed, either by throwing an error or by abnormally terminating |
|
The job has been disabled and has changed to the |
|
The job completed because it reached its |
|
The job was stopped by a call to |
|
The job's schedule limit was reached. The job was not started because the delay in starting the job exceeded the value of the |
|
The job was disabled by the Scheduler or by a call to |
|
A job running a chain was put into the |
|
Not an event, but a constant that provides an easy way for you to enable all events |
|
A job run either failed, succeeded, or was stopped |
Table 93-52 lists program attribute values.
Note:
See theCREATE_PROGRAM
procedure for more complete descriptions of the attributes in this table.Table 93-52 Program Attribute Values
Name | Description |
---|---|
|
The action that the program performs, depending on the |
|
The type of program. This must be one of the following supported program types: ' |
|
The number of arguments required by the stored procedure or other executable that the program invokes |
|
An optional comment. This can describe what the program does, or give usage details. |
Table 93-53 lists job class attribute values.
Note:
See theCREATE_JOB_CLASS
procedure for more complete descriptions of the attributes in this table.Table 93-53 Job Class Attribute Values
Name | Description |
---|---|
|
The resource consumer group a class is associated with. If |
|
The database service that the jobs in the job class have affinity to. If service is set, |
|
This attribute specifies how much information is logged. The three possible options are:
|
|
This enables you to control the amount of logging the Scheduler performs. To prevent the job log and the window log from growing indiscriminately, the Scheduler has an attribute that specifies how much history (in days) to keep. Once a day, the Scheduler will automatically purge all log entries from both the job log as well as the window log that are older than the specified history. The default is 30 days. You can change the default by using the
The range of valid values is 1 through 999. |
|
An optional comment about the class. |
Table 93-54 lists window attribute values.
Note:
See theCREATE_WINDOW
procedure for more complete descriptions of the attributes in this table.Table 93-54 Window Attribute Values
Name | Description |
---|---|
|
The resource plan to be associated with a window. When the window opens, the system will switch to this resource plan. When the window closes, the original resource plan will be restored. If a resource plan has been made active with the Only one resource plan can be associated with a window. It may be |
|
The priority of the window. Must be one of ' |
|
The duration of the window. |
|
The name of a schedule to use with this window. If this is set, |
|
A string using the calendaring syntax. PL/SQL date functions are not allowed. If this is set, |
|
The next date on which this window is scheduled to open. If this is set, |
|
The date after which the window will no longer open. If this is set, |
|
An optional comment about the window. |
Table 93-55 lists window group attribute values.
Table 93-55 Window Group Attribute Values
Name | Description |
---|---|
|
An optional comment about the window group. |
Table 93-56 lists schedule attribute values.
Note:
See theCREATE_SCHEDULE
and CREATE_CALENDAR_SCHEDULE
procedures for more complete descriptions of the attributes in this table.Table 93-56 Schedule Attribute Values
Name | Description |
---|---|
|
An expression using the calendaring syntax. See "Calendaring Syntax" for more information. |
|
An optional comment. |
|
The cutoff date after which the schedule will not specify any dates. |
|
The start or reference date used by the calendaring syntax. |
|
This attribute takes two values: the |
Table 93-57 lists chain attribute values.
Note:
See theCREATE_CHAIN
procedure for more complete descriptions of the attributes in this table.Table 93-57 Chain Attribute Values
Name | Description |
---|---|
|
If this is not For most chains, the normal evaluation times are sufficient. Because evaluation of a large chain is CPU intensive, this attribute should be used only when chain rules use SQL syntax and the rule conditions contain elements that are not under the control of the Scheduler. |
|
An optional comment describing the purpose of the chain. |
This procedure sets an attribute of an object to NULL
. What attributes can be set depends on the object being altered. If the object is enabled, it will be disabled before being altered and be reenabled afterward. If the object cannot be reenabled, an error is generated and the object will be left in a disabled state.
DBMS_SCHEDULER.SET_ATTRIBUTE_NULL ( name IN VARCHAR2, attribute IN VARCHAR2);
Table 93-58 SET_ATTRIBUTE_NULL Procedure Parameters
Parameter | Description |
---|---|
|
The name of the object |
|
The attribute being changed |
To run SET_ATTRIBUTE_NULL
for a window, window group, or job class, you must have the MANAGE
SCHEDULER
privilege. Otherwise, you must be the owner of the object being altered or have ALTER
privileges on that object or have the CREATE
ANY
JOB
privilege.
This procedure sets the value for an argument of the associated program for a job, encapsulated in an AnyData
object. It overrides any default value set for the program argument. NULL
is a valid assignment for a program argument. The argument can be specified by position or by name. You can specify by name only when:
The job points to a saved program object
The argument was assigned a name with the DEFINE_ANYDATA_ARGUMENT Procedure
No type checking of the argument is done at any time by the Scheduler.
SET_JOB_ANYDATA_VALUE
is overloaded.
Sets a program argument by its position.
DBMS_SCHEDULER.SET_JOB_ANYDATA_VALUE ( job_name IN VARCHAR2, argument_position IN PLS_INTEGER, argument_value IN SYS.ANYDATA);
Sets a program argument by its name.
DBMS_SCHEDULER.SET_JOB_ANYDATA_VALUE ( job_name IN VARCHAR2, argument_name IN VARCHAR2, argument_value IN SYS.ANYDATA);
Table 93-59 SET_JOB_ANYDATA_VALUE Procedure Parameters
Parameter | Description |
---|---|
|
The name of the job to be altered |
|
The name of the program argument being set |
|
The position of the program argument being set |
|
The new value to be assigned to the program argument, encapsulated in an |
SET_JOB_ANYDATA_VALUE
requires that you be the owner of the job or have ALTER
privileges on that job. You can also set a job argument value if you have the CREATE
ANY
JOB
privilege.
This procedure sets the value of an argument of the associated program for a job. It overrides any default value set for the program argument. NULL
is a valid assignment for a program argument. The argument can be specified by position or by name. You can specify by name only when:
The job points to a saved program object
The argument was assigned a name with the DEFINE_PROGRAM_ARGUMENT Procedures or the DEFINE_METADATA_ARGUMENT Procedure
No type checking of the argument is done at any time by the Scheduler.
SET_JOB_ARGUMENT_VALUE
is overloaded.
Sets an argument value by position:
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE ( job_name IN VARCHAR2, argument_position IN PLS_INTEGER, argument_value IN VARCHAR2);
Sets an argument value by name:
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE ( job_name IN VARCHAR2, argument_name IN VARCHAR2, argument_value IN VARCHAR2);
Table 93-60 SET_JOB_ARGUMENT_VALUE Procedure Parameters
Parameter | Description |
---|---|
|
The name of the job to be altered |
|
The name of the program argument being set |
|
The position of the program argument being set |
|
The new value to be set for the program argument. To set a non- |
SET_JOB_ARGUMENT_VALUE
requires that you be the owner of the job or have ALTER
privileges on that job. You can also set a job argument value if you have the CREATE
ANY
JOB
privilege.
This procedure sets the value of a Scheduler attribute. This takes effect immediately but the resulting changes may not be seen immediately. The attributes you can set are default_timezone
, max_job_slave_processes
, and log_history
.
DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE ( attribute IN VARCHAR2, value IN VARCHAR2);
Table 93-61 SET_SCHEDULER_ATTRIBUTE Procedure Parameters
Parameter | Description |
---|---|
|
The name of the Scheduler attribute. Possible values are:
|
|
The new value of the attribute |
To run SET_SCHEDULER_ATTRIBUTE
, you must have the MANAGE
SCHEDULER
privilege.
This procedure stops currently running jobs or all jobs in a job class. Any instance of the job will be stopped. After stopping the job, the state of a one-time job will be set to STOPPED
whereas the state of a repeating job will be set to SCHEDULED
or COMPLETED
depending on whether the next run of the job is scheduled.
If a job pointing to a chain is stopped, all steps of the running chain that are running will be stopped.
DBMS_SCHEDULER.STOP_JOB ( job_name IN VARCHAR2 force IN BOOLEAN DEFAULT FALSE);
Table 93-62 STOP_JOB Procedure Parameters
Parameter | Description |
---|---|
|
The name of the job or job class. Can be a comma-delimited list. For a job class, the If the name of a job class is specified, the jobs that belong to that job class are stopped. The job class is not affected by this call. |
|
If If Use of the |
STOP_JOB
without the force
option requires that you be the owner of the job or have ALTER
privileges on that job. You can also stop a job if you have the CREATE
ANY
JOB
or MANAGE
SCHEDULER
privilege.
STOP_JOB
with the force
option requires that you have the MANAGE
SCHEDULER
privilege.