ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
Purpose
Schedule jobs based on schedules and events
AUTHID
CURRENT_USER
Commit Semantics
Name
Description
ABSORB_ERRORS
Tries to absorb errors and attempts to make the listed job attribute changes and commits all successful changes
STOP_ON_FIRST_ERROR
The default: Returns on the first error and the previous attribute changes that were successful are committed to disk
TRANSACTIONAL
Returns on the first error and everything that happened before that error is rolled back
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;
-- file watcher
TYPE scheduler_filewatcher_result IS OBJECT (
destination VARCHAR2(4000),
directory_path VARCHAR2(4000),
actual_file_name VARCHAR2(4000),
file_size NUMBER,
file_timestamp TIMESTAMP WITH TIME ZONE,
ts_ms_from_epoch NUMBER,
matching_requests SYS.SCHEDULER_FILEWATCHER_REQ_LIST);
TYPE scheduler_filewatcher_request IS OBJECT (
owner VARCHAR2(4000),
name VARCHAR2(4000),
requested_path_name VARCHAR2(4000),
requested_file_name VARCHAR2(4000),
credential_owner VARCHAR2(4000),
credential_name VARCHAR2(4000),
min_file_size NUMBER,
steady_state_dur NUMBER);
TYPE bylist IS VARRAY (256) OF PLS_INTEGER;
Dependencies
SELECT name FROM dba_dependencies WHERE referenced_name = 'DBMS_SCHEDULER'
UNION
SELECT referenced_name FROM dba_dependencies WHERE name = 'DBMS_SCHEDULER';
-- query returns 153 rows
Documented
No
Exceptions
Error Code
Reason
ORA-27476
"<agent_name_or_program_name>" does not exist
ORA-27494
operation not permitted on lightweight and in-memory jobs
ORA-27498
resource constraint object type mismatch
First Available
10.1
Security Model
Owned by SYS with EXECUTE granted to AUDSYS, MDSYS,
ORACLE_OCM and PUBLIC
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.
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';
Add an email notification to an existing scheduler job
dbms_scheduler.add_job_email_notification(
job_name IN VARCHAR2,
recipients IN VARCHAR2,
sender IN VARCHAR2 DEFAULT NULL,
subject IN VARCHAR2 DEFAULT dbms_scheduler.default_notification_subject,
body IN VARCHAR2 DEFAULT dbms_scheduler.default_notification_body,
events IN VARCHAR2 DEFAULT 'JOB_FAILED,JOB_BROKEN,JOB_SCH_LIM_REACHED,
JOB_CHAIN_STALLED,JOB_OVER_MAX_DUR',
filter_condition IN VARCHAR2 DEFAULT NULL);
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);
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);
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;
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;
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;
BEGIN
dbms_scheduler.create_event_schedule('uwclass.file_arrival', SYSTIMESTAMP,
'tab.user_data.object_owner = ''UWCLASS'' AND
tab.user_data.event_name = ''FILE_ARRIVAL'' AND
extract hour from tab.user_data.event_timestamp < 9',
'entry_events_q');
Creates a file watcher, which is a Scheduler object that defines the location, name, and other properties of a file whose arrival on a system causes the Scheduler to start a job
dbms_scheduler.create_file_watcher(
file_watcher_name IN VARCHAR2,
directory_path IN VARCHAR2,
file_name IN VARCHAR2,
credential_name IN VARCHAR2,
destination IN VARCHAR2 DEFAULT NULL,
min_file_size IN PLS_INTEGER DEFAULT 0,
steady_state_duration IN INTERVAL DAY TO SECOND DEFAULT NULL,
comments IN VARCHAR2 DEFAULT NULL,
enabled IN BOOLEAN DEFAULT TRUE);
Creates a group to be set as a job destination. Groups can be Windows Groups, Database Destination Groups, or External Destination Groups.
dbms_scheduler.create_group(
group_name IN VARCHAR2,
group_type IN VARCHAR2, -- DB_DEST, EXTERNAL_DEST, or WINDOW
member IN VARCHAR2 DEFAULT NULL,
comments IN VARCHAR2 DEFAULT NULL);
Creates an incompatibility definition but not for lightweight or in-memory jobs
dbms_scheduler.create_incompatibility(
incompatibility_name IN VARCHAR2,
object_name IN VARCHAR2,
constraint_level IN VARCHAR2 DEFAULT 'JOB_LEVEL',
enabled IN BOOLEAN DEFAULT TRUE,
comments IN VARCHAR2 DEFAULT NULL);
Create a job in a single call (without using an existing program or schedule).
This demo create a job that runs the load_vip_table stored procedure every 3rd Saturday of the month at 11:15.
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,
credential_name IN VARCHAR2 DEFAULT NULL,
destination_name IN VARCHAR2 DEFAULT NULL);
BEGIN
sys.dbms_scheduler.create_job (
job_name => 'UWCLASS.VIP_TABLE_LOAD',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN uwclass.load_vip_table; END; ',
start_date => TRUNC(SYSDATE+4)+(11.25/24),
repeat_interval => 'FREQ=MONTHLY; BYDAY=3SAT',
end_date => NULL,
job_class => 'DEFAULT_JOB_CLASS',
comments => 'Populate the VIP table with names and site codes');
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 DEFAULT NULL,
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,
credential_name IN VARCHAR2 DEFAULT NULL,
destination_name 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',
credential_name IN VARCHAR2 DEFAULT NULL,
destination_name IN VARCHAR2 DEFAULT NULL);
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',
credential_name IN VARCHAR2 DEFAULT NULL,
destination_name IN VARCHAR2 DEFAULT NULL);
See Scheduler Demo1 Below
Create a job using a 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',
credential_name IN VARCHAR2 DEFAULT NULL,
destination_name IN VARCHAR2 DEFAULT NULL);
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,
credential_name IN VARCHAR2 DEFAULT NULL,
destination_name IN VARCHAR2 DEFAULT NULL);
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_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);
BEGIN
sys.dbms_scheduler.create_program(
program_name => 'ProcessBackorders1',
program_type => 'PLSQL_BLOCK',
program_action => 'BEGIN uwclass.process_backorders; END;',
comments => 'Create POs from backorders');
Specifies resources used by jobs or creates a new resource
dbms_scheduler.create_resource(
resource_name IN VARCHAR2,
units IN PLS_INTEGER,
status IN VARCHAR2 DEFAULT 'ENFORCE_CONSTRAINTS',
constraint_level IN VARCHAR2 DEFAULT 'JOB_LEVEL',
comments IN VARCHAR2 DEFAULT NULL);
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');
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);
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);
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);
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 Above
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);
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);
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);
conn sys@pdbdev as sysdba
GRANT create any job TO uwclass;
conn uwclass/uwclass@pdbdev
CREATE TABLE t (
col1 NUMBER,
col2 DATE);
BEGIN
dbms_scheduler.create_program('MLPROG', 'plsql_block', 'INSERT INTO t VALUES (1, SYSDATE);
user_lock.sleep(1);INSERT INTO uwclass.t VALUES (1, TRUE)');
END;
/
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;
/
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);
Deletes a file from one or more specified destination hosts. Uses a specified credential to login to the given hosts that must have an execution agent installed and running.
The caller must have the CREATE EXTERNAL JOB system privilege and have EXECUTE privileges on the credential.
dbms_scheduler.delete_file(
destination_file IN VARCHAR2,
destination_host IN VARCHAR2,
credential_name IN VARCHAR2,
destination_permissions IN VARCHAR2 DEFAULT NULL);
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,
-- interpret this as <owner.job_name> from dba_scheduler_jobs
force IN BOOLEAN DEFAULT FALSE,
commit_semantics IN VARCHAR2 DEFAULT 'STOP_ON_FIRST_ERROR');
SELECT owner, job_name, state, enabled
FROM dba_scheduler_jobs;
DECLARE
CURSOR jcur IS
SELECT owner || '.' || job_name AS JNAME
FROM dba_scheduler_jobs
WHERE owner NOT IN (SELECT username FROM dba_users WHERE oracle_maintained
= 'Y')
AND state = 'SCHEDULED';
BEGIN
FOR jrec IN jcur LOOP
dbms_scheduler.disable(jrec.jname,
TRUE);
END LOOP;
END;
/
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');
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';
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);
Get information about the running agent. Possible attributes are 'ALL', 'VERSION', 'RUNNING_JOBS', 'NUMBER_OF_RUNNING_JOBS', and 'UPTIME'.
dbms_scheduler.get_agent_info(
agent_name IN VARCHAR2,
attribute IN VARCHAR2)
RETURN VARCHAR2;
col agent_name format a30
SELECT *
FROM dba_aq_agents;
DECLARE
retVal VARCHAR2(30);
BEGIN
retVal := dbms_scheduler.get_agent_info('SCHEDULER$_REMDB_AGENT','VERSION');
dbms_output.put_line(retVal);
END;
/
DECLARE
*
ERROR at line 1:
ORA-27476: "SYS"."SCHEDULER$_REMDB_AGENT" does not exist
ORA-06512: at "SYS.DBMS_ISCHED", line 5057
ORA-06512: at "SYS.DBMS_ISCHED", line 9064
ORA-29257: host SCHEDULER$_REMDB_AGENT unknown
ORA-06512: at "SYS.DBMS_ISCHED", line 7740
ORA-06512: at "SYS.DBMS_SCHEDULER", line 4240
ORA-06512: at line 4
Returns the version of a Scheduler Execution Agent
dbms_scheduler.get_agent_version(agent_host IN VARCHAR2) RETURN VARCHAR2;
desc dba_scheduler_db_dests
DECLARE
retVal VARCHAR2(100);
BEGIN
retVal := dbms_scheduler.get_agent_version('ILM_AGENT');
dbms_output.put_line(RetVal);
END;
/
DECLARE
*
ERROR at line 1:
ORA-27476: "SYS.TEST" does not exist
ORA-06512: at "SYS.DBMS_ISCHED", line 4921
ORA-06512: at "SYS.DBMS_ISCHED", line 8619
ORA-29257: host TEST unknown
ORA-06512: at "SYS.DBMS_ISCHED", line 7415
ORA-06512: at "SYS.DBMS_SCHEDULER", line 3998
ORA-06512: at line 4
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 sys@pdbdev 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);
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;
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);
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);
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);
Run a job immediately. If use_current_session is TRUE the job is run in the user's current session: if FALSE the job is run in the background by a dedicated job slave.
Note: There is may still be a delay if resources are not available.
dbms_scheduler.run_job(
job_name IN VARCHAR2,
use_current_session IN BOOLEAN DEFAULT TRUE,
event_message IN SYS.ANYDATA DEFAULT NULL);
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_ae
WHERE created > SYSDATE-1
AND object_name NOT LIKE 'W%';
dbms_scheduler.show_errors(error_list OUT SYS.SCHEDULER$_BATCHERR_ARRAY);
DECLARE
errList sys.scheduler$_batcherr_array;
BEGIN
dbms_scheduler.show_errors(errList);
dbms_output.put_line(errList.COUNT);
END;
/
DECLARE
*
ERROR at line 1:
ORA-06531: Reference to uninitialized collection
ORA-06512: at line 5
dbms_scheduler.submit_remote_external_job(
job_name IN VARCHAR2,
job_subname IN VARCHAR2,
job_owner IN VARCHAR2,
command IN VARCHAR2,
arguments IN ODCIVARCHAR2LIST,
credential_name IN VARCHAR2,
credential_owner IN VARCHAR2,
destination IN VARCHAR2,
destination_owner IN VARCHAR2,
destination_name IN VARCHAR2,
job_dest_id IN VARCHAR2,
job_action IN VARCHAR2,
job_scheduled_start IN TIMESTAMP WITH TIME ZONE,
job_start IN TIMESTAMP WITH TIME ZONE,
window_start IN TIMESTAMP WITH TIME ZONE,
window_end IN TIMESTAMP WITH TIME ZONE,
chainid IN VARCHAR2,
request_id IN NUMBER,
log_id IN NUMBER,
logging_level IN NUMBER,
store_output IN NUMBER,
connect_credential_name IN VARCHAR2,
connect_credential_owner IN VARCHAR2);
PRAGMA SUPPLEMENTAL_LOG_DATA(submit_remote_external_job, NONE);
CREATE OR REPLACE PROCEDURE load_data (inval IN NUMBER) IS
BEGIN
FOR i IN 1..3 LOOP
user_lock.sleep(100);
INSERT INTO t VALUES (inval, SYSDATE);
END LOOP;
END load_data;
/
DECLARE
jname VARCHAR2(30);
BEGIN
dbms_scheduler.set_scheduler_attribute('MAX_JOB_SLAVE_PROCESSES', 2);
FOR i IN 1 .. 100 LOOP
jname := 'JOB' || TO_CHAR(i);
INSERT INTO locations
(location_id, latitude, longitude)
VALUES
(locid, latit, longi);
EXCEPTION
WHEN NO_DATA_FOUND THEN
EXIT;
END;
END LOOP;
COMMIT;
utl_file.fclose(vSFile);
runno := runno + 1;
utl_file.frename('CTEMP',fname || TO_CHAR(yearno) || '.dat',
'CTEMP', TO_CHAR(runno) || '.arc', TRUE);
EXCEPTION
WHEN OTHERS THEN
NULL;
END load_data;
END sched_demo;
/
-- test procedure
exec sched_demo.load_data('0101', 2008);
SELECT * FROM locations;
TRUNCATE TABLE locations;
-- rename 1.arc back to 01012008.dat
-- create a program with all job arguments
-- requires create job privilege
BEGIN
dbms_scheduler.create_program(
program_name => 'Run_LOAD_DATA',
program_type => 'STORED_PROCEDURE',
program_action => 'SCHED_DEMO.LOAD_DATA',
number_of_arguments => 2,
enabled => FALSE,
comments => 'UW Test Scheduled Load');
END;
/
desc all_scheduler_programs
col owner format a10
col program_name format a25
col program_action format a45
col comments format a55
SELECT owner, program_name, program_type, program_action
FROM all_scheduler_programs;
SELECT owner, program_name, enabled, comments
FROM all_scheduler_programs;
-- set program argument
SELECT overload, position, argument_name, data_type
FROM all_arguments
WHERE object_name = 'SCHED_DEMO.LOAD_DATA';
SELECT job_name, program_name, start_date
FROM all_scheduler_jobs;
-- set scheduler attributes
col value format a50
SELECT attribute_name, value
FROM all_scheduler_global_attribute;
-- requires manage scheduler privilege
BEGIN
dbms_scheduler.set_scheduler_attribute('MAX_JOB_SLAVE_PROCESSES', 2);
END;
/
SELECT attribute_name, value
FROM all_scheduler_global_attribute;
-- enable the program
exec dbms_scheduler.enable('Run_LOAD_DATA');
-- enable the job
exec dbms_scheduler.enable('UW_File_Load');
SELECT *
FROM locations;
-- test the job
exec dbms_scheduler.run_job('UW_File_Load', TRUE);
SELECT *
FROM locations;
col additional_info format a25
SELECT job_name, operation, status, additional_info
FROM all_scheduler_job_log
WHERE owner = 'UWCLASS';
SELECT job_name, state, run_count, next_run_date
FROM all_scheduler_jobs;
-- watch the job run renaming files as required to avoid a conflict
-- clean up
BEGIN
-- stop the job
BEGIN
dbms_scheduler.stop_job('UW_File_Load', TRUE);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
-- drop program argument
dbms_scheduler.drop_program_argument('Run_LOAD_DATA', 1);
dbms_scheduler.drop_program_argument('Run_LOAD_DATA', 2);
-- disable the program
dbms_scheduler.disable('Run_LOAD_DATA', TRUE);
-- drop the program
dbms_scheduler.drop_program('Run_LOAD_DATA', TRUE);
-- drop the job
dbms_scheduler.drop_job('UW_File_Load', TRUE);
END;
/
Scheduled External Job: Demo 3
-- if the job is not owned by SYS then edit $ORACLE_HOME/rdbms/admin and
-- edit the file externaljob.ora make run_user=oracle and run_group=dba
BEGIN
dbms_scheduler.set_job_argument_value('EXT_LOAD', 1,
argument_value=>'userid=uwclass/uwclass@pdbdev control=c:\temp\sqlldr02.ctl log=c:\temp\sqlldr02.log');
END;
/
exec dbms_scheduler.enable('EXT_LOAD');
col status format a10
col additional_info format a80
SELECT job_name, operation, status
FROM all_scheduler_job_log
WHERE owner = 'SYS';
SELECT job_name, additional_info
FROM all_scheduler_job_run_details;
exec dbms_scheduler.disable('EXT_LOAD');
exec dbms_scheduler.drop_job('EXT_LOAD');
Scheduler File Watcher: Demo 4
conn sys@pdbdev as sysdba
CREATE OR REPLACE DIRECTORY stage AS '/stage';
GRANT read, write ON DIRECTORY stage TO uwclass;
GRANT execute ON sys.scheduler_filewatcher_result TO uwclass;
--GRANT create job TO uwclass;
--GRANT manage scheduler TO uwclass;
--GRANT execute ON dbms_lock to watcher_user;
--GRANT execute ON dbms_system to watcher_user;
-- copy the demo file to c:\temp\test.txt
col directory_path format a10
col file_name format a15
SELECT file_watcher_name, directory_path, file_name, credential_name, enabled
FROM dba_scheduler_file_watchers;
SELECT * FROM t_staging_files;
-- tear it down
BEGIN
dbms_scheduler.drop_file_watcher('UW_Fwatch', TRUE);
dbms_scheduler.drop_program('file_watcher');
dbms_scheduler.drop_credential('uw_credential', TRUE);
END;
/