| General Information |
| Source |
{ORACLE_HOME}/rdbms/admin/dbmssch.sql |
| First Available |
10.1 |
Constants |
| Name |
Data Type |
Value |
| General |
| logging_off |
PLS_INTEGER |
32 |
| logging_runs |
PLS_INTEGER |
64 |
| logging_failed_runs |
PLS_INTEGER |
128 |
| logging_full |
PLS_INTEGER |
256 |
| Raise Flag Events |
| job_started |
PLS_INTEGER |
1 |
| job_succeeded |
PLS_INTEGER |
2 |
| job_failed |
PLS_INTEGER |
4 |
| job_broken |
PLS_INTEGER |
8 |
| job_completed |
PLS_INTEGER |
16 |
| job_stopped |
PLS_INTEGER |
32 |
| job_sch_lim_reached |
PLS_INTEGER |
64 |
| job_disabled |
PLS_INTEGER |
128 |
| job_chain_stalled |
PLS_INTEGER |
256 |
| job_all_events |
PLS_INTEGER |
511 |
| job_over_max_dur |
PLS_INTEGER |
512 |
| job_run_completed |
PLS_INTEGER |
job_succeeded+job_failed+job_stopped; |
|
Commit Semantics |
| ABSORB_ERRORS |
Procedure tries to absorb any errors and attempts the rest of the job attribute changes on the list and commits all the changes that were successful. |
| STOP_ON_FIRST_ERROR |
Procedure returns on the first error and the previous attribute changes that were successful are committed to disk. This is the
default |
| TRANSACTIONAL |
Procedure returns on the first error and everything that happened before that error is rolled back |
|
Dependencies |
SELECT name FROM dba_dependencies
WHERE referenced_name = 'DBMS_SCHEDULER'
UNION
SELECT referenced_name FROM dba_dependencies
WHERE name = 'DBMS_SCHEDULER';
|
Defined Data Types |
| TYPE bylist IS VARRAY (256) OF PLS_INTEGER; |
| |
| Name |
Constant |
Data Type |
Value |
| Yearly |
Constant |
PLS_INTEGER |
1 |
| Monthly |
Constant |
PLS_INTEGER |
2 |
| Weekly |
Constant |
PLS_INTEGER |
3 |
| Daily |
Constant |
PLS_INTEGER |
4 |
| Hourly |
Constant |
PLS_INTEGER |
5 |
| Minutely |
Constant |
PLS_INTEGER |
6 |
| Secondly |
Constant |
PLS_INTEGER |
7 |
| |
| Monday |
Constant |
INTEGER |
1 |
| Tuesday |
Constant |
INTEGER |
2 |
| Wednesday |
Constant |
INTEGER |
3 |
| Thursday |
Constant |
INTEGER |
4 |
| Friday |
Constant |
INTEGER |
5 |
| Saturday |
Constant |
INTEGER |
6 |
| Sunday |
Constant |
INTEGER |
7 |
TYPE SCHEDULER$_RULE_LIST IS TABLE OF sys.schedule;
/
TYPE SCHEDULER$_STEP_TYPE_LIST IS TABLE OF sys.sch;
/
TYPE SCHEDULER$_CHAIN_LINK_LIST IS TABLE OF sys.sc;
/
TYPE SCHEDULER$_STEP_TYPE IS OBJECT (
step_name VARCHAR2(32),
step_type VARCHAR2(32));
/
TYPE RE$VARIABLE_VALUE AS OBJECT (
variable_name VARCHAR2(32),
variable_data sys.anydata)
-- For the definition of RE$NV_LIST:
SELECT dbms_metadata.get_ddl('TYPE', 'RE$NV_LIST)
FROM dual;
|
| Security Model |
Execute is granted to PUBLIC with GRANT
OPTION
-- which is just plain stupid and should be revoked upon installation.
AUTHID CURRENT_USER |
Security Privileges |
| Privilege |
Description |
| Create Any
Jobs |
This privilege enables you to create, alter, and drop jobs, chains, schedules, and programs in any schema except SYS. This privilege is very powerful and should be used with care because it allows the grantee to execute code as any other user. |
| Create External Jobs |
Required to create jobs that run outside of the database. Owners of jobs of type 'EXECUTABLE' or jobs that point to programs of type 'EXECUTABLE' require this
privilege. To run a job of type 'EXECUTABLE', you must have this privilege and the CREATE JOB privilege. |
| Create Job |
This privilege enables you to create jobs, chains, schedules, and programs in your own schema. You will always be able to alter and drop jobs, schedules and programs in your own schema, even if you do not have the CREATE JOB privilege. In this case, the job must have been created in your schema by another user with the CREATE ANY JOB privilege. |
| Execute Any Class |
Enables jobs to run under any job class |
| Execute Any
Program |
Enables jobs to use programs or chains from any schema |
| Manage
Scheduler |
This is the most important privilege for administering the Scheduler. It enables you to create, alter, and drop job classes, windows, and window groups. It also enables you to set and retrieve Scheduler attributes and purge Scheduler logs. |
|
System Privileges to Create Chains |
dbms_rule_adm.grant_system_privilege(dbms_rule_adm.create_rule_obj, '<schema_name>')
dbms_rule_adm.grant_system_privilege(
dbms_rule_adm.create_rule_set_obj, '<schema_name>')
dbms_rule_adm.grant_system_privilege(
dbms_rule_adm.create_evaluation_context_obj, '<schema_name>')
|
|
BEGIN
dbms_rule_adm.grant_system_privilege(
dbms_rule_adm.create_rule_obj, 'uwclass'),
dbms_rule_adm.grant_system_privilege(
dbms_rule_adm.create_rule_set_obj, 'uwclass'),
dbms_rule_adm.grant_system_privilege(
dbms_rule_adm.create_evaluation_context_obj, 'uwclass')
END;
/
|
| |
| ADD_EVENT_QUEUE_SUBSCRIBER |
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); |
SELECT owner, name
FROM dba_queues
ORDER BY 2,1;
set linesize 121
col retention format a20
SELECT queue_table, max_retries, retry_delay, retention
FROM dba_queues
WHERE name = 'SCHEDULER$_EVENT_QUEUE';
select consumer_name
from dba_queue_subscribers
where queue_name = 'SCHEDULER$_EVENT_QUEUE';
exec dbms_scheduler.add_event_queue_subscriber('UWCLASS');
SELECT consumer_name
FROM dba_queue_subscribers
WHERE queue_name = 'SCHEDULER$_EVENT_QUEUE';
exec dbms_scheduler.remove_event_queue_subscriber('UWCLASS');
SELECT consumer_name
FROM dba_queue_subscribers
WHERE queue_name = 'SCHEDULER$_EVENT_QUEUE'; |
| |
| ADD_WINDOW_GROUP_MEMBER |
Adds one or more windows to an existing window group |
dbms_scheduler.add_window_group_member(
group_name IN VARCHAR2,
window_list IN VARCHAR2); |
| See
CREATE_WINDOW_GROUP Demo
|
| |
| ALTER_CHAIN (new
11g overload) |
Alters steps of a chain
Overload 1 |
dbms_scheduler.alter_chain(
chain_name IN VARCHAR2,
step_name IN VARCHAR2,
attribute IN VARCHAR2,
value IN BOOLEAN); |
| See CREATE_CHAIN Demo |
Overload 2
|
dbms_scheduler.alter_chain(
chain_name IN VARCHAR2,
step_name IN VARCHAR2,
attribute IN VARCHAR2,
char_value IN VARCHAR2); |
| TBD |
| |
| ALTER_RUNNING_CHAIN |
Alters steps of a running chain
Overload 1 |
dbms_scheduler.alter_running_chain(
job_name IN VARCHAR2,
step_name IN VARCHAR2,
attribute IN VARCHAR2,
value IN
BOOLEAN); |
| See CREATE_CHAIN Demo |
Overload 2 |
dbms_scheduler.alter_running_chain(
job_name IN VARCHAR2,
step_name IN VARCHAR2,
attribute IN VARCHAR2,
value IN VARCHAR2); |
| TBD |
| |
| ANALYZE_CHAIN |
Analyzes a chain or a list of steps and rules and outputs a list of chain dependencies |
dbms_scheduler.analyze_chain(
chain_name IN VARCHAR2,
rules IN sys.scheduler$_rule_list,
steps IN sys.scheduler$_step_type_list,
step_pairs OUT sys.scheduler$_chain_link_list); |
| TBD |
| |
| AUTO_PURGE |
| Purges from the logs based on class and global log_history |
dbms_scheduler.auto_purge; |
| exec dbms_scheduler.auto_purge; |
| |
| CHECK_SYS_PRIVS |
Internal / Undocumented |
dbms_scheduler.check_sys_privs RETURN PLS_INTEGER; |
conn uwclass/uwclass
SELECT dbms_scheduler.check_sys_privs
FROM dual;
conn / as sysdba
SELECT dbms_scheduler.check_sys_privs
FROM dual;
|
| |
| CLOSE_WINDOW |
| Closes an open
window prematurely. A closed window means that it is no longer in effect. |
dbms_scheduler.close_window(window_name
IN VARCHAR2);
|
| exec
dbms_scheduler.close_window('weeknights'); |
| |
| CONVERT_DBMS_JOB
(new 11g) |
Convert a dbms_job api created job into a scheduler job the dbms_job equivalent job will be removed.
As this is officially undocumented I am
leaving this as it is until I learn more.
|
dbms_scheduler.convert_dbms_job(job_name IN VARCHAR2); |
|
Step 1: Create a job
CREATE OR REPLACE PROCEDURE test_job IS
BEGIN
NULL;
END test_job;
/
DECLARE
JobNo user_jobs.job%TYPE;
BEGIN
dbms_job.submit(JobNo,
'begin test_job; end;', SYSDATE,
'SYSDATE + 36/86400');
COMMIT;
END;
/
SELECT job, schema_user
FROM dba_jobs;
Step 2: Convert it
-- This might actually work if DBMS_JOB created jobs had VARCHAR2 names.
-- Unfortunately they do not so I can not puzzle this one out unless
I
-- assume the information in the rdbms/admin file is incorrect.
|
| |
| COPY_JOB |
Copy a job. The new_job will contain all the attributes of the old_job, except that it will be created disabled |
dbms_scheduler.copy_job(old_job IN VARCHAR2,
new_job IN VARCHAR2); |
SELECT owner, job_name, enabled
FROM dba_scheduler_jobs
exec dbms_scheduler.copy_job('PURGE_LOG',
'TEST');
SELECT owner, job_name, enabled
FROM dba_scheduler_jobs;
exec dbms_scheduler.drop_job('TEST', TRUE);
SELECT owner, job_name, enabled
FROM dba_scheduler_jobs; |
| |
| CREATE_CALENDAR_STRING |
Undocumented |
dbms_scheduler.create_calendar_string(
frequency IN PLS_INTEGER,
interval IN
PLS_INTEGER,
bysecond IN bylist,
byminute IN bylist,
byhour IN
bylist,
byday_days IN bylist,
byday_occurrence IN bylist,
bymonthday IN bylist,
byyearday IN bylist,
byweekno IN bylist,
bymonth IN bylist,
calendar_string OUT VARCHAR2); |
| TBD |
| |
| CREATE_CHAIN |
Creates a chain. Chains are created disabled and must be enabled before use. |
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); |
desc dba_scheduler_chains
SELECT owner, chain_name, rule_set_owner, rule_set_name, number_of_rules
FROM dba_scheduler_chains;
exec dbms_scheduler.create_chain('TEST_CHAIN');
SELECT owner, chain_name, rule_set_owner, rule_set_name, number_of_rules
FROM dba_scheduler_chains;
desc dba_scheduler_chain_steps
SELECT chain_name, step_name, program_name, step_type
FROM dba_scheduler_chain_steps;
BEGIN
dbms_scheduler.define_chain_step('TEST_CHAIN',
'STEP1', 'PROGRAM1');
dbms_scheduler.define_chain_step('TEST_CHAIN',
'STEP2', 'PROGRAM2');
END;
/
SELECT chain_name, step_name, program_name,
event_schedule_name
FROM dba_scheduler_chain_steps;
BEGIN
dbms_scheduler.define_chain_event_step('TEST_CHAIN','STEP2','SCHED1');
END;
/
SELECT chain_name, step_name, program_name,
event_schedule_name
FROM dba_scheduler_chain_steps;
desc dba_scheduler_chain_rules
SELECT chain_name, rule_name, condition, action
FROM dba_scheduler_chain_rules;
BEGIN
dbms_scheduler.define_chain_rule('TEST_CHAIN','TRUE',
'START step1', 'step1_rule', 'begin chain run');
dbms_scheduler.define_chain_rule('TEST_CHAIN', 'step1 completed',
'START step2', 'step2_rule');
END;
/
SELECT chain_name, rule_name, condition, action
FROM dba_scheduler_chain_rules;
exec dbms_scheduler.enable('TEST_CHAIN');
desc dba_scheduler_jobs
col job_name format a30
col job_type format a16
col job_action format a70
col repeat_interval format a28
SELECT job_name, job_type, job_action
FROM dba_scheduler_jobs;
BEGIN
dbms_scheduler.create_job('JOB1', job_type => 'CHAIN',
job_action => 'TEST_CHAIN',
repeat_interval => 'freq=daily;byhour=22;byminute=30;bysecond=0',
enabled => TRUE);
END;
/
SELECT job_name, job_type, job_action
FROM dba_scheduler_jobs;
BEGIN
dbms_scheduler.alter_chain ('TEST_CHAIN',
'STEP1',
attribute => 'SKIP', value => TRUE);
END;
/
BEGIN
dbms_scheduler.run_chain('TEST_CHAIN',
'JOB1', start_steps =>
'JOB_STEP1, JOB_STEP2');
END;
/
BEGIN
dbms_scheduler.alter_running_chain ('TEST_CHAIN',
'JOB1', 'STEP2',
attribute => 'PAUSE', value => TRUE);
END;
/
exec dbms_scheduler.drop_chain_rule('TEST_CHAIN',
'STEP1_RULE', TRUE);
exec dbms_scheduler.drop_chain_step('TEST_CHAIN',
'STEP2', TRUE);
exec dbms_scheduler.disable('TEST_CHAIN');
exec dbms_scheduler.drop_chain('TEST_CHAIN'); |
| |
| CREATE_CREDENTIAL
(new 11g) |
Create a new credential
|
dbms_scheduler.create_credential(
credential_name IN VARCHAR2,
username IN VARCHAR2, -- operating
system user
password IN VARCHAR2, -- and
corresponding pwd
database_role IN VARCHAR2 DEFAULT NULL,
windows_domain IN VARCHAR2 DEFAULT NULL,
comments IN VARCHAR2 DEFAULT NULL); |
desc
dba_scheduler_credentials
col owner format a5
col username format a20
SELECT owner, credential_name, username
FROM dba_scheduler_credentials;
BEGIN
dbms_scheduler.create_credential('uw_credential',
'oracle', 'oracle1');
END;
/
SELECT owner, credential_name, username
FROM dba_scheduler_credentials;
GRANT EXECUTE ON uw_credential TO uwclass;
SELECT grantee, owner, table_name, privilege
FROM dba_tab_privs
WHERE grantee = 'UWCLASS';
exec dbms_scheduler.drop_credential('uw_credential',
TRUE); |
| |
| CREATE_EVENT_SCHEDULE |
Create a named event schedule |
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);
|
desc
dba_scheduler_schedules
col schedule_name format a20
col event_condition format a15
SELECT schedule_name, start_date, event_queue_name, event_condition
FROM dba_scheduler_schedules;
-- to run the following an appropriate queue must be
-- created the AQ code to do this is not included here
BEGIN
dbms_scheduler.create_event_schedule('TEST_EVENTS_SCHED',
SYSTIMESTAMP,
event_condition => 'tab.user_data.event_type = ''ZERO_BALANCE''',
queue_spec => 'entry_events_q, entry_agent1');
END;
/
SELECT schedule_name, start_date, event_queue_name, event_condition
FROM dba_scheduler_schedules;
exec dbms_scheduler.drop_schedule( 'TEST_EVENTS_SCHED',
TRUE); |
| |
| CREATE_JOB (new
11g parameter in overloads 3 and 4) |
Create a job in a single call (without using an existing program or schedule).
Overload 1 |
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); |
| TBD |
Create a job using an inlined event schedule
Overload 2 |
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,
event_condition IN VARCHAR2,
queue_spec IN VARCHAR2,
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); |
| TBD |
Create a job using an existing, named, schedule object and a named program
object
Overload 3
|
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
job_style IN VARCHAR2 DEFAULT
'REGULAR'); |
| TBD |
Create a job using an existing, named, program object and an inlined
schedule
Overload 4
|
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,
job_style IN VARCHAR2 DEFAULT
'REGULAR'); |
| See Scheduler Demo1: Below |
Create a job using named program and inlined event schedule
Overload 5 |
dbms_scheduler.create_job(
job_name IN VARCHAR2,
program_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,
job_class IN VARCHAR2 DEFAULT 'DEFAULT_JOB_CLASS',
enabled IN BOOLEAN DEFAULT FALSE,
auto_drop IN BOOLEAN DEFAULT TRUE,
comments IN VARCHAR2 DEFAULT
NULL,
job_style IN VARCHAR2 DEFAULT
'REGULAR'); |
| TBD |
Create a job using a named schedule object and an inlined program
Overload 6 |
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); |
| TBD |
| |
| CREATE_JOB_CLASS |
Create a job class |
dbms_scheduler.create_job_class(
job_class_name VARCHAR2,
resource_consumer_group VARCHAR2 DEFAULT NULL,
service VARCHAR2
DEFAULT NULL,
logging_level PLS_INTEGER DEFAULT DBMS_SCHEDULER.LOGGING_RUNS,
log_history PLS_INTEGER DEFAULT NULL,
comments VARCHAR2
DEFAULT NULL); |
|
desc
dba_scheduler_job_classes
col logging_level format a15
SELECT job_class_name, resource_consumer_group, logging_level
FROm dba_scheduler_job_classes;
BEGIN
dbms_resource_manager.create_pending_area;
dbms_resource_manager.create_consumer_group('Workers', 'Those that do
actual work');
dbms_resource_manager.submit_pending_area;
dbms_scheduler.create_job_class('finance_jobs',
'Workers');
END;
/
SELECT job_class_name, resource_consumer_group,
logging_level
FROm dba_scheduler_job_classes;
exec dbms_scheduler.drop_job_class('finance_jobs', TRUE);
BEGIN
dbms_resource_manager.create_pending_area;
dbms_resource_manager.delete_consumer_group('Workers');
dbms_resource_manager.submit_pending_area;
END;
/ |
| |
| CREATE_JOBS (new
11g) |
Batch create job
|
dbms_scheduler.create_jobs(
job_array IN SYS.JOB_ARRAY,
commit_semantics IN VARCHAR2 DEFAULT 'STOP_ON_FIRST_ERROR'); |
| TBD |
| |
| CREATE_PROGRAM |
Create a new 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); |
| See Scheduler Demo1: Below |
| |
| CREATE_SCHEDULE |
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);
|
col owner format a6
col schedule_name format a25
col start_date format a35
col repeat_interval format a25
SELECT owner, schedule_name, schedule_type, start_date, repeat_interval
FROM dba_scheduler_schedules;
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;
/
SELECT owner, schedule_name, schedule_type, start_date, repeat_interval
FROM dba_scheduler_schedules;
BEGIN
dbms_scheduler.create_schedule('job2_sched', repeat_interval =>
'embed_sched+OFFSET:15D');
END;
/
SELECT owner, schedule_name, schedule_type, start_date, repeat_interval
FROM dba_scheduler_schedules;
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;
/
SELECT owner, schedule_name, schedule_type, start_date, repeat_interval
FROM dba_scheduler_schedules;
BEGIN
dbms_scheduler.create_schedule('fifth_day_off', repeat_interval =>
'FREQ=retail_fiscal_year;BYDAY=SAT,SUN;BYPERIOD=2,4;BYSETPOS=5');
END;
/
SELECT owner, schedule_name, schedule_type, start_date, repeat_interval
FROM dba_scheduler_schedules;
BEGIN
dbms_scheduler.drop_schedule('MAIN_SCHED');
dbms_scheduler.drop_schedule('JOB2_SCHED');
dbms_scheduler.drop_schedule('YEAR_START');
dbms_scheduler.drop_schedule('RETAIL_FISCAL_YEAR');
dbms_scheduler.drop_schedule('FIFTH_DAY_OFF');
dbms_scheduler.drop_schedule('EMBED_SCHED');
END;
/
SELECT owner, schedule_name, schedule_type,
start_date, repeat_interval
FROM dba_scheduler_schedules;
|
| |
| CREATE_WINDOW
(new 11g overload) |
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.
Overload 1
|
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); |
|
desc dba_scheduler_windows
col window_name format a16
col schedule_owner format a10
col next_start_date format a40
SELECT window_name, resource_plan, window_priority, next_start_date
FROM dba_scheduler_windows;
BEGIN
dbms_resource_manager.create_pending_area;
dbms_resource_manager.create_plan('UW_PLAN', 'Sched Demo',
'RATIO');
dbms_resource_manager.create_consumer_group('Workers', 'Those that do
actual work');
dbms_resource_manager.create_plan_directive(plan=>'UW_PLAN',
group_or_subplan=>'Workers', comment=>'Can Grab All The CPU', cpu_p1=>100);
dbms_resource_manager.create_plan_directive(plan=>'UW_PLAN',
group_or_subplan=>'OTHER_GROUPS', comment=>'Testing', cpu_p2=>0);
dbms_resource_manager.submit_pending_area;
END;
/
BEGIN
dbms_scheduler.create_schedule('maint_sched',
repeat_interval=>
'FREQ=YEARLY;BYDATE=0201^SPAN:1W;BYDAY=SUN');
dbms_scheduler.create_window(
window_name => 'weeknights',
resource_plan => 'UW_PLAN',
schedule_name => 'maint_sched',
duration => INTERVAL '4' HOUR,
window_priority => 'HIGH',
comments => 'Off-hours maintenance window');
END;
/
SELECT window_name, resource_plan, window_priority, next_start_date
FROM dba_scheduler_windows;
exec dbms_scheduler.drop_window('WEEKNIGHTS');
exec dbms_scheduler.drop_schedule('MAINT_SCHED');
BEGIN
dbms_resource_manager.create_pending_area;
dbms_resource_manager.delete_plan_cascade('UW_PLAN');
dbms_resource_manager.submit_pending_area;
END;
/
select plan, group_or_subplan, mgmt_p1 from resource_plan_directive$; |
Overload 2
|
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); |
|
TBD |
| |
| CREATE_WINDOW_GROUP |
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); |
desc
dba_scheduler_window_groups
col window_group_name format a24
col comments format a38
SELECT window_group_name, enabled, next_start_date, comments
FROM dba_scheduler_window_groups;
SELECT window_name
FROM dba_scheduler_windows;
BEGIN
dbms_scheduler.create_window_group(
group_name => 'downtime',
window_list => 'monday_window, wednesday_window, friday_window',
comments => 'Group of system maintenance windows');
END;
/
SELECT window_group_name, enabled, next_start_date, comments
FROM dba_scheduler_window_groups;
SELECT window_group_name, number_of_windows
FROM dba_scheduler_window_groups;
exec dbms_scheduler.add_window_group_member('downtime',
'tuesday_window, thursday_window,
saturday_window');
SELECT window_group_name, number_of_windows
FROM dba_scheduler_window_groups;
exec dbms_scheduler.remove_window_group_member('downtime',
'wednesday_window, thursday_window');
SELECT window_group_name, number_of_windows
FROM dba_scheduler_window_groups;
exec dbms_scheduler.drop_window_group('downtime');
SELECT window_group_name, number_of_windows
FROM dba_scheduler_window_groups; |
| |
| DEFINE_ANYDATA_ARGUMENT |
Define an argument with a default value encapsulated in an ANYDATA data type |
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); |
| TBD |
| |
| DEFINE_CHAIN_EVENT_STEP |
Adds or replaces a chain step and associates it with an inline schedule
Overload 1 |
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); |
| See CREATE_CHAIN Demo |
Adds or replaces a chain step and associates it with an inline event
Overload 2 |
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); |
| TBD |
| |
| DEFINE_CHAIN_RULE |
Adds or replaces a chain rule |
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); |
| See CREATE_CHAIN Demo |
| |
| DEFINE_CHAIN_STEP |
Adds or replaces a chain step and associates it with a program or chain |
dbms_scheduler.define_chain_step(
chain_name IN VARCHAR2,
step_name IN VARCHAR2,
program_name IN VARCHAR2); |
| See CREATE_CHAIN Demo |
| |
| DEFINE_METADATA_ARGUMENT |
Define a special metadata argument for the program |
dbms_scheduler.define_metadata_argument(
program_name IN VARCHAR2,
metadata_attribute IN VARCHAR2,
argument_position IN PLS_INTEGER,
argument_name IN VARCHAR2 DEFAULT NULL);
|
Metadata Attributes |
|
Type |
Data Type |
Description |
| event_message |
TIMESTAMP WITH
TIMEZONE |
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 USER_DATA column of the queue table. |
| job_name |
VARCHAR2 |
Name of the currently running job |
| job_owner |
VARCHAR2 |
Owner of the currently running job |
| job_subname |
VARCHAR2 |
Subname of the currently running job. The name + subname form a unique identifier for a job that is running a chain step. NULL if the job is not part of a chain. |
| window_end |
TIMESTAMP WITH
TIMEZONE |
If the job was started by a window, the time that the window is scheduled to close |
| window_start |
TIMESTAMP WITH
TIMEZONE |
If the job was started by a window, the time that the window opened |
|
desc
dba_scheduler_programs
col program_action format a50
SELECT program_name, program_type, program_action
FROM dba_scheduler_programs;
CREATE OR REPLACE PROCEDURE load_data(job_name
VARCHAR2) IS
BEGIN
NULL;
END load_data;
/
BEGIN
dbms_scheduler.create_program(
program_name => 'Run_LOAD_DATA',
program_type => 'STORED_PROCEDURE',
program_action => 'LOAD_DATA',
number_of_arguments => 1,
enabled => FALSE,
comments => 'UW Test Scheduled Load');
END;
/
SELECT program_name, program_type, program_action
FROM dba_scheduler_programs;
exec dbms_scheduler.define_metadata_argument('Run_LOAD_DATA',
'JOB_NAME', 1);
exec dbms_scheduler.drop_program('Run_LOAD_DATA');
drop procedure load_data; |
| |
| DEFINE_PROGRAM_ARGUMENT |
Define an argument of a program
Overload 1 |
dbms_scheduler.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); |
| See Scheduler Demos:
Below |
Overload 2 |
dbms_scheduler.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); |
| TBD |
| |
| DISABLE (new 11g
parameter) |
|
Disable a program, chain, job, window or window_group. The procedure will NOT return an
error if the object was already disabled. |
dbms_scheduler.disable(
name
IN VARCHAR2,
force IN BOOLEAN
DEFAULT FALSE,
commit_semantics IN VARCHAR2 DEFAULT 'STOP_ON_FIRST_ERROR'); |
| See Scheduler Demo1: Below |
| |
| DISABLE1_CALENDAR_CHECK |
| Undocumented import
helper function |
dbms_scheduler.disable1_calendar_check; |
| dbms_scheduler.disable1_calendar_check; |
| |
| DROP_CHAIN |
Drop a chain |
dbms_scheduler.drop_chain(
chain_name IN VARCHAR2,
force IN BOOLEAN DEFAULT FALSE); |
| See CREATE_CHAIN Demo |
| |
| DROP_CHAIN_RULE |
| Drop a chain rule |
dbms_scheduler.drop_chain_rule(
chain_name IN VARCHAR2,
rule_name IN VARCHAR2,
force IN BOOLEAN DEFAULT FALSE); |
| See CREATE_CHAIN Demo |
| |
| DROP_CHAIN_STEP |
| Drop a chain step |
dbms_scheduler.drop_chain_step(
chain_name IN VARCHAR2,
step_name IN VARCHAR2,
force IN BOOLEAN DEFAULT FALSE); |
| See CREATE_CHAIN Demo |
| |
| DROP_CREDENTIAL
(new 11g) |
Drops an existing credential (or a comma separated list of credentials) |
dbms_scheduler.drop_credential(
credential_name IN VARCHAR2,
force IN BOOLEAN DEFAULT FALSE); |
| See
CREATE_CREDENTAIAL Demo |
| |
| DROP_JOB (new 11g
parameter) |
| Drop a job or several jobs
|
dbms_scheduler.drop_job(
job_name IN VARCHAR2,
force IN BOOLEAN
DEFAULT FALSE,
defer IN BOOLEAN
DEFAULT FALSE,
commit_semantics IN VARCHAR2 DEFAULT 'STOP_ON_FIRST_ERROR'); |
| See Scheduler Demo1:
Below |
| |
| DROP_JOB_CLASS |
| Drop a job class |
dbms_scheduler.drop_job_class(
job_class_name IN VARCHAR2,
force IN BOOLEAN DEFAULT FALSE); |
| See CREATE_JOB_CLASS
Demo |
| |
| DROP_PROGRAM |
| Drops an existing program (or a comma separated list of programs) |
dbms_scheduler.drop_program(
program_name IN VARCHAR2,
force IN BOOLEAN DEFAULT FALSE); |
| See Scheduler Demo1: Below |
| |
| DROP_PROGRAM_ARGUMENT |
Drop a program argument either by name or position
Overload 1 |
dbms_scheduler.drop_program_argument(
program_name IN VARCHAR2,
argument_position IN PLS_INTEGER); |
| See Scheduler Demo1: Below |
| Overload 2 |
dbms_scheduler.drop_program_argument(
program_name IN VARCHAR2,
argument_name IN VARCHAR2); |
| TBD |
| |
| DROP_SCHEDULE |
| Drop a schedule (or comma-separated list of schedules) |
dbms_scheduler.drop_schedule(
schedule_name IN VARCHAR2,
force IN BOOLEAN DEFAULT FALSE); |
| See CREATE_SCHEDULE
and CREATE_WINDOW Demos |
| |
| DROP_WINDOW |
| 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); |
| See CREATE_WINDOW
Demo |
| |
| DROP_WINDOW_GROUP |
|
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); |
| See
CREATE_WINDOW_GROUP Demo |
| |
| ENABLE (new 11g
parameter) |
|
Enable a program, chain, job, window or window group. The procedure will not
return an error if the object was already enabled. |
dbms_scheduler.enable(
name
IN VARCHAR2,
commit_semantics IN VARCHAR2 DEFAULT 'STOP_ON_FIRST_ERROR');); |
| See Scheduler Demo1:
Below |
| |
| END_DETACHED_JOB_RUN
(new 11g) |
| Undocumented |
dbms_scheduler.end_detached_job_run (
job_name IN VARCHAR2,
error_number IN PLS_INTEGER DEFAULT 0,
additional_info IN VARCHAR2 DEFAULT NULL); |
| TBD |
| |
| EVALUTE_CALENDAR_STRING |
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); |
set serveroutput on;
alter session set NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
DECLARE
start_date TIMESTAMP;
return_date_after TIMESTAMP;
next_run_date TIMESTAMP;
BEGIN
start_date := TO_TIMESTAMP_TZ('01-JAN-2006 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;
/ |
| |
| EVALUTE_RUNNING_CHAIN |
| Forces immediate evaluation of a running chain |
dbms_scheduler.evaluate_running_chain(job_name IN VARCHAR2); |
| TBD |
| |
| GENERATE_EVENT_LIST |
| Undocumented |
dbms_scheduler.generate_event_list(statusvec
IN NUMBER) RETURN VARCHAR2 |
| TBD |
| |
| GENERATE_JOB_NAME |
Returns a unique name for a job |
dbms_scheduler.generate_job_name(prefix IN VARCHAR2 DEFAULT 'JOB$_')
RETURN VARCHAR2; |
SELECT dbms_scheduler.generate_job_name
FROM dual;
SELECT dbms_scheduler.generate_job_name('UW')
FROM dual; |
| |
| GET_ATTRIBUTE |
Retrieve an
attribute
Overload 1 |
dbms_scheduler.get_attribute(
name IN VARCHAR2,
attribute IN VARCHAR2,
value OUT PLS_INTEGER); |
| TBD |
Overload 2 |
dbms_scheduler.get_attribute(
name IN VARCHAR2,
attribute IN VARCHAR2,
value OUT BOOLEAN); |
conn / as sysdba
set serveroutput on
DECLARE
x BOOLEAN;
BEGIN
dbms_scheduler.get_attribute('DEFAULT_JOB_CLASS',
'SYSTEM',
x);
IF x THEN
dbms_output.put_line('True');
ELSE
dbms_output.put_line('False');
END IF;
END;
/ |
| Overload 3 |
dbms_scheduler.get_attribute(
name IN VARCHAR2,
attribute IN VARCHAR2,
value OUT DATE); |
| TBD |
| Overload 4 |
dbms_scheduler.get_attribute(
name IN VARCHAR2,
attribute IN VARCHAR2,
value OUT TIMESTAMP); |
| TBD |
| Overload 5 |
dbms_scheduler.get_attribute(
name IN VARCHAR2,
attribute IN VARCHAR2,
value OUT TIMESTAMP WITH TIME ZONE); |
| TBD |
| Overload 6 |
dbms_scheduler.get_attribute(
name IN VARCHAR2,
attribute IN VARCHAR2,
value OUT TIMESTAMP WITH LOCAL TIME ZONE); |
| TBD |
| Overload 7 |
dbms_scheduler.get_attribute(
name IN VARCHAR2,
attribute IN VARCHAR2,
value OUT INTERVAL DAY TO SECOND); |
| TBD |
| Overload 8 |
dbms_scheduler.get_attribute(
name IN VARCHAR2,
attribute IN VARCHAR2,
value OUT VARCHAR2); |
| TBD |
| Overload 9 |
dbms_scheduler.get_attribute(
name IN VARCHAR2,
attribute IN VARCHAR2,
value OUT VARCHAR2,
value2 OUT VARCHAR2); |
| TBD |
| |
| GET_CHAIN_RULE_ACTION |
| Used by chain views to output rule actions |
dbms_scheduler.get_chain_rule_action(action_in IN re$nv_list)
RETURN VARCHAR2; |
| TBD |
| |
| GET_CHAIN_RULE_CONDITION |
| Used by chain views to output rule conditions |
dbms_scheduler.get_chain_rule_condition(
action_in IN re$nv_list,
condition_in IN VARCHAR2)
RETURN VARCHAR2; |
| TBD |
| |
| GET_DEFAULT_VALUE |
| Accepts an attribute name and returns the default value. If the
attribute is not recognized it returns NULL. If the attribute is of type BOOLEAN, it will return 'TRUE' or 'FALSE'. |
dbms_scheduler.get_default_value(attribute_name
IN VARCHAR2)
RETURN VARCHAR2; |
SELECT
*
FROM dba_scheduler_global_attribute;
SELECT dbms_scheduler.get_default_value('LOG_HISTORY')
FROM dual; |
| |
| GET_FILE (new
11g) |
Retrieves a file from a specified destination host
Overload 1
|
dbms_scheduler.get_file (
source_file IN VARCHAR2,
source_host IN VARCHAR2,
credential_name IN VARCHAR2,
file_contents IN OUT NOCOPY CLOB CHARACTER SET
ANY_CS); |
| TBD |
Overload 2 |
dbms_scheduler.get_file (
source_file IN VARCHAR2,
source_host IN VARCHAR2,
credential_name IN VARCHAR2,
file_contents IN OUT NOCOPY BLOB); |
| TBD |
Overload 3 |
dbms_scheduler.get_file (
source_file
IN VARCHAR2,
source_host
IN VARCHAR2,
credential_name
IN VARCHAR2,
destination_file_name
IN VARCHAR2,
destination_directory_object IN VARCHAR2,
destination_permissions
IN VARCHAR2 DEFAULT NULL); |
| TBD |
| |
| GET_JOB_STEP_CF |
| Undocumented |
dbms_scheduler.get_job_step_cf (
iec VARCHAR2,
icn VARCHAR2,
vname VARCHAR2,
iev SYS.RE$NV_LIST)
RETURN SYS.RE$VARIABLE_VALUE; |
| TBD |
|
|
| GET_SCHEDULER_ATTRIBUTE |
| Get the value of a scheduler attribute |
dbms_scheduler.get_scheduler_attribute(
attribute IN VARCHAR2,
value OUT VARCHAR2); |
| See
SET_SCHEDULER_ATTRIBUTE Demo: Below |
| |
| GET_SYS_TIME_ZONE_NAME |
| Returns
the current time zone setting |
dbms_scheduler.get_sys_time_zone_name RETURN VARCHAR2 |
SELECT dbms_scheduler.get_sys_time_zone_name
FROM dual; |
| |
| GET_VARCHAR2_VALUE |
Converts SYS.ANYDATA to VARCHAR2 |
dbms_scheduler.get_varchar2_value(a IN
SYS.ANYDATA) RETURN VARCHAR2; |
CREATE TABLE t (mycol sys.anyData);
INSERT INTO t
VALUES (sys.anyData.convertVarchar2('hello world'));
SELECT * FROM t;
SELECT dbms_scheduler.get_varchar2_value(mycol)
FROM t; |
| |
| OPEN_WINDOW |
| Opens a window independent of its
schedule |
dbms_scheduler_open_window(
window_name IN VARCHAR2,
duration IN INTERVAL DAY TO SECOND,
force IN BOOLEAN DEFAULT FALSE); |
| exec dbms_scheduler.open_window('weeknights', INTERVAL '4'
HOUR); |
| |
| PURGE_LOG |
Purges from the logs based on the arguments. The default is to purge all entries |
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); |
SELECT, COUNT(*)
FROM dba_scheduler_job_run_details;
exec dbms_scheduler.purge_log;
SELECT, COUNT(*)
FROM dba_scheduler_job_run_details; |
| |
| PUT_FILE (new
11g) |
Saves a file to one or more specified destination hosts
Overload 1
|
dbms_scheduler.put_file (
destination_file IN VARCHAR2,
destination_host IN VARCHAR2,
credential_name IN VARCHAR2,
file_contents IN CLOB CHARACTER SET ANY_CS,
destination_permissions IN VARCHAR2 DEFAULT NULL); |
| TBD |
Overload 2 |
dbms_scheduler.put_file (
destination_file IN VARCHAR2,
destination_host IN VARCHAR2,
credential_name IN VARCHAR2,
file_contents
IN BLOB,
destination_permissions IN VARCHAR2 DEFAULT NULL); |
| TBD |
Overload 3 |
dbms_scheduler.put_file (
destination_file IN VARCHAR2,
destination_host IN VARCHAR2,
credential_name IN VARCHAR2,
source_file_name
IN VARCHAR2,
source_directory_object IN VARCHAR2,
destination_permissions IN VARCHAR2 DEFAULT NULL); |
| TBD |
| |
| REGISTER_REMOTE_DATABASE
(new 11g) |
Undocumented |
dbms_scheduler.register_remote_database(
database_name IN VARCHAR2,
registered_as IN VARCHAR2,
database_link IN VARCHAR2 DEFAULT NULL,
replace IN BOOLEAN DEFAULT FALSE); |
| TBD |
| |
| REMOVE_EVENT_QUEUE_SUBSCRIBER |
| Remove subscriber from the SCHEDULER queue |
dbms_scheduler.remove_event_queue_subscriber(
subscriber_name IN VARCHAR2 DEFAULT NULL); |
| See ADD_EVENT_QUEUE_SUBSCRIBER Demo: Above |
| |
| REMOVE_WINDOW_GROUP_MEMBER |
| Removes one or more windows from an existing window group |
dbms_scheduler.remove_window_group_member(
group_name IN VARCHAR2,
window_list IN VARCHAR2);
|
| See CREATE_WINDOW
Demo |
| |
| RESET_JOB_ARGUMENT_VALUE |
Clear a previously set job argument value
Overload 1 |
dbms_scheduler.reset_job_argument_value(
job_name IN VARCHAR2,
argument_position IN PLS_INTEGER); |
|
exec dbms_scheduler.reset_job_argument_value('UW_File_Load', 2); |
| Overload 2 |
dbms_scheduler.reset_job_argument_value(
job_name IN VARCHAR2,
argument_name IN VARCHAR2); |
|
exec dbms_scheduler.reset_job_argument_value('UW_File_Load', 'YEARNO'); |
| |
| RESOLVE_CALENDAR_STRING |
Undocumented
Overload 1 |
dbms_scheduler.resolve_calendar_string(
calendar_string IN VARCHAR2,
frequency OUT PLS_INTEGER,
interval OUT PLS_INTEGER,
calendars_used OUT BOOLEAN,
bysecond OUT scheduler$_int_array_type,
byminute OUT scheduler$_int_array_type,
byhour OUT scheduler$_int_array_type,
byday_days OUT scheduler$_int_array_type,
byday_occurrence OUT scheduler$_int_array_type,
bydate_y OUT scheduler$_int_array_type,
bydate_md OUT scheduler$_int_array_type,
bymonthday OUT scheduler$_int_array_type,
byyearday OUT scheduler$_int_array_type,
byweekno OUT scheduler$_int_array_type,
bymonth OUT scheduler$_int_array_type,
bysetpos OUT scheduler$_int_array_type); |
| TBD |
Overload
2 |
dbms_scheduler.resolve_calendar_string(
calendar_string IN VARCHAR2,
frequency OUT PLS_INTEGER,
interval OUT PLS_INTEGER,
bysecond OUT BYLIST,
byminute OUT BYLIST,
byhour OUT BYLIST,
byday_days OUT BYLIST,
byday_occurrence OUT BYLIST,
bymonthday OUT BYLIST,
byyearday OUT BYLIST,
byweekno OUT BYLIST,
bymonth OUT BYLIST); |
| TBD |
| |
| RESOLVE_NAME |
Retrieve the canonicalized object owner or name |
dbms_scheduler.resolve_name(
full_name IN VARCHAR2,
default_owner IN VARCHAR2,
return_part IN NUMBER)
RETURN VARCHAR2; |
SELECT dbms_scheduler.resolve_name('SERVERS', 'UWCLASS', 1)
FROM dual;
SELECT dbms_scheduler.resolve_name('SERVERS', 'UWCLASS', 2)
FROM dual; |
| |
| RUN_CHAIN |
Immediately runs a job pointing to a chain starting with a list of specified steps. The job will be started in the background.
Overload 1 |
dbms_scheduler.run_chain(
chain_name IN VARCHAR2,
start_steps IN VARCHAR2,
job_name IN VARCHAR2 DEFAULT NULL); |
| TBD |
Immediately runs a job pointing to a chain starting with the given list of step states
Overload 2 |
dbms_scheduler.run_chain(
chain_name IN VARCHAR2,
step_state_list IN SYS.SCHEDULER$_STEP_TYPE_LIST,
job_name IN VARCHAR2 DEFAULT NULL); |
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;
/ |
| |
| RUN_JOB |
Run a job immediately |
dbms_scheduler.run_job(
job_name IN VARCHAR2,
use_current_session IN BOOLEAN DEFAULT TRUE);
TRUE = synchronous, FALSE = asynchronous |
| See Scheduler Demo1: Below |
| |
| SET_AGENT_REGISTRATION_PASS
(new 11g) |
Set the remote execution agent registration password for this database
optionally limit the password to a limited number of uses or to before a
specified expiry date |
dbms_scheduler.set_agent_registration_pass(
registration_password IN VARCHAR2,
expiration_date IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
max_uses
IN PLS_INTEGER DEFAULT NULL); |
BEGIN
dbms_scheduler.set_agent_registration_pass('N0!', SYSTIMESTAMP+1,
3);
END;
/
SELECT owner, object_name, object_type
FROM dba_objects
WHERE created > SYSDATE-1
AND object_name NOT LIKE 'W%'; |
| |
| SET_ATTRIBUTE |
Sets an attribute of a scheduler object
Overload 1 |
dbms_scheduler.set_attribute(
name IN VARCHAR2,
attribute IN VARCHAR2,
value IN BOOLEAN); |
| dbms_scheduler.set_attribute(jobname,
'restartable', TRUE); |
| Overload 2 |
dbms_scheduler.set_attribute(
name IN VARCHAR2,
attribute IN VARCHAR2,
value IN VARCHAR2,
value2 IN VARCHAR2 DEFAULT NULL); |
| TBD |
| Overload 3 |
dbms_scheduler.set_attribute(
name IN VARCHAR2,
attribute IN VARCHAR2,
value IN DATE); |
| TBD |
| Overload 4 |
dbms_scheduler.set_attribute(
name IN VARCHAR2,
attribute IN VARCHAR2,
value IN TIMESTAMP); |
| TBD |
| Overload 5 |
dbms_scheduler.set_attribute(
name IN VARCHAR2,
attribute IN VARCHAR2,
value IN TIMESTAMP WITH TIME ZONE); |
| TBD |
| Overload 6 |
dbms_scheduler.set_attribute(
name IN VARCHAR2,
attribute IN VARCHAR2,
value IN TIMESTAMP WITH LOCAL TIME ZONE); |
| TBD |
| Overload 7 |
dbms_scheduler.set_attribute(
name IN VARCHAR2,
attribute IN VARCHAR2,
value IN INTERVAL DAY TO SECOND); |
| TBD |
| |
| SET_ATTRIBUTE_NULL |
| Sets an attribute of a scheduler program to NULL |
dbms_scheduler.set_attribute_null(
name IN VARCHAR2,
attribute IN VARCHAR2); |
| exec dbms_scheduler.set_attribute(jobname,
'restartable'); |
| |
| SET_JOB_ANYDATA_VALUE |
Set a value to be passed to one of the arguments of a program using the ANYDATA data type
Overload 1 |
dbms_scheduler.set_job_anydata_value(
job_name IN VARCHAR2,
argument_position IN PLS_INTEGER,
argument_value IN SYS.ANYDATA); |
| TBD |
| Overload 2 |
dbms_scheduler.set_job_anydata_value(
job_name IN VARCHAR2,
argument_name IN VARCHAR2,
argument_value IN SYS.ANYDATA); |
| TBD |
| |
| SET_JOB_ARGUMENT_VALUE |
Set a value to be passed to one of the arguments of the program
Overload 1 |
dbms_scheduler.set_job_argument_value(
job_name IN VARCHAR2,
argument_position IN PLS_INTEGER,
argument_value IN VARCHAR2); |
| exec
dbms_scheduler.set_job_argument_value('UW_File_Load', 2, '2007'); |
| Overload 2 |
dbms_scheduler.set_job_argument_value(
job_name IN VARCHAR2,
argument_name IN VARCHAR2,
argument_value IN VARCHAR2); |
|
exec dbms_scheduler.set_job_argument_value('UW_File_Load', 'YEARNO', '2007'); |
| |
| SET_JOB_ATTRIBUTES
(new 11g) |
Batch set job attribute |
dbms_scheduler.set_job_attributes(
jobattr_array IN SYS.JOBATTR_ARRAY,
commit_semantics IN VARCHAR2 DEFAULT 'STOP_ON_FIRST_ERROR'); |
| TBD |
| |
| SET_SCHEDULER_ATTRIBUTE |
Set the value of a scheduler attribute. This takes effect immediately, but the resulting changes may not be seen immediately. |
dbms_scheduler.set_scheduler_attribute(
attribute IN VARCHAR2,
value IN VARCHAR2); |
set serveroutput on
DECLARE
x VARCHAR2(100);
BEGIN
dbms_scheduler.get_scheduler_attribute('DEFAULT_TIMEZONE', x);
dbms_output.put_line('DTZ: ' || x);
dbms_scheduler.get_scheduler_attribute('EVENT_EXPIRY_TIME', x);
dbms_output.put_line('EET: ' || x);
dbms_scheduler.get_scheduler_attribute('LOG_HISTORY', x);
dbms_output.put_line('LH: ' || x);
dbms_scheduler.get_scheduler_attribute('MAX_JOB_SLAVE_PROCESSES', x);
dbms_output.put_line('MJSP: ' || x);
END;
/
BEGIN
dbms_scheduler.set_scheduler_attribute('MAX_JOB_SLAVE_PROCESSES', 3);
END;
/
DECLARE
x VARCHAR2(100);
BEGIN
dbms_scheduler.get_scheduler_attribute('DEFAULT_TIMEZONE', x);
dbms_output.put_line('DTZ: ' || x);
dbms_scheduler.get_scheduler_attribute('EVENT_EXPIRY_TIME', x);
dbms_output.put_line('EET: ' || x);
dbms_scheduler.get_scheduler_attribute('LOG_HISTORY', x);
dbms_output.put_line('LH: ' || x);
dbms_scheduler.get_scheduler_attribute('MAX_JOB_SLAVE_PROCESSES', x);
dbms_output.put_line('MJSP: ' || x);
END;
/
|
| |
| SHOW_ERRORS (new
11g) |
|
Batch show errors |
|