| |
 |
|
Oracle dbms_scheduler examples
Oracle Database Tips by Donald Burleson |
For more working details and a complete code
depot, see the wonderful $16.95 book
Oracle Job Scheduling
by Dr.
Timothy Hall. You can get the best deal (30%-off by)
buying it directly from the publisher.
The
dbms_scheduler
package was introduced in Oracle 10g, so the
example code associated with these sections will
not work on previous versions.
Where appropriate, Enterprise Manager (EM)
screen shots will be used to illustrate the
GUI/Web alternative to using the PL/SQL API. Job
Scheduling in regards to its benefit for the DBA
is covered in Chapter 6 with emphasis on the
dbms_job and
dbms_scheduler packages.
The example code shows how objects can be
created, manipulated and dropped.
In a number of cases, code examples rely
on previously created objects, which may have
already been dropped, so they will have to be
recreated before it will be possible to move on.
The following section will detail how to set up
a test environment to enable the running of any
example code.
Setting up a Test Environment
In order to use the examples in this chapter, it
is necessary to create a user ID to work with
and define a task to schedule.
The following code creates a user called
job_user
and grants it the necessary privileges.
Some privileges used are specific for
Oracle 11g and should be ignored if a prior
version is used.
conn sys/password as
sysdba
-- Create user.
create user job_user
identified by job_user default tablespace
users quota unlimited on users;
grant connect to job_user;
grant select_catalog_role
to job_user;
-- Privileges for task,
not for dbms_job.
grant create procedure to
job_user;
grant execute on dbms_lock
to job_user;
grant execute on
dbms_system to job_user;
-- Oracle 10g only.
grant create job to
job_user;
grant manage scheduler to
job_user;
conn job_user/job_user
The MANAGE SCHEDULER
privilege should only be granted when a user
must administer job classes, windows and window
groups.
These objects provide a link between the
scheduler and the resource manager, a feature
which had traditionally required the DBA role.
The roles and privileges associated with
the 11g scheduler will be presented in the
following text.
In the previous script, a system privilege and
an object privilege were granted to
job_user
to allow the creation of a task to schedule.
The following script creates a database
procedure that will be used throughout this book
when creating jobs.
This procedure uses the
dbms_system
package to write a user defined string to the
alert log at the start and end of the job.
The body of the procedure loops 100 times with a
sleep of one second in each loop.
It also uses the
dbms_application_info
package
to write information to the
v$session
and
v$session_longops
views.
The use of the
dbms_system
and
dbms_application_info
packages will be covered in more detail later in
this text.
--
*************************************************
-- Parameters:
--
1) Text to identify this test job.
--
*****************************************************************
CREATE OR REPLACE
PROCEDURE my_job_proc (p_text
IN
VARCHAR2) AS
l_rindex
PLS_INTEGER;
l_slno
PLS_INTEGER;
l_total
NUMBER;
l_obj
PLS_INTEGER;
BEGIN
SYS.DBMS_SYSTEM.ksdwrt(2, 'MY_JOB_PROC
Start: ' || p_text);
DBMS_APPLICATION_INFO.set_module(
module_name => 'my_job_proc',
action_name => p_text || ': Start.');
l_rindex
:=
Dbms_Application_Info.Set_Session_Longops_Nohint;
l_total := 100;
FOR i IN 1 .. l_total
LOOP
DBMS_APPLICATION_INFO.set_action(
action_name => p_text || ': Sleep '
|| i || ' of ' || l_total || '.');
DBMS_APPLICATION_INFO.set_session_longops(
rindex
=> l_rindex,
slno
=> l_slno,
op_name
=> 'MY_JOB_PROC',
target
=> l_obj,
context
=> 0,
sofar
=> i,
totalwork
=> l_total,
target_desc => 'MY_JOB_PROC',
units
=> 'loops');
DBMS_LOCK.sleep(1);
END
LOOP;
DBMS_APPLICATION_INFO.set_action(
action_name => p_text || ': End.');
SYS.DBMS_SYSTEM.ksdwrt(2, 'MY_JOB_PROC
End: ' || p_text);
END;
/
SHOW ERRORS
The procedure can be tested by calling it from
SQL*Plus
as follows:
SQL> exec my_job_proc('Test
It!');
Once the procedure has completed, the alert log
should contain an entry that looks similar to
the following:
Sat Jun 19
12:29:16
2004
MY_JOB_PROC Start: Test It!
Sat Jun 19
12:30:59
2004
MY_JOB_PROC End: Test It!
Obviously, these entries may be separated by
other messages depending on what else has
happened on the instance during the time it took
for the job to run.
Now that the user named
job_user
has been created and granted privileges, it is
time to schedule jobs.
The first step is the examination of the
dbms_job
package.
Oracle Job Schedulers examples
The following code examples rely on the
previously defined programs and schedules to show how the overloads
of the create_job procedure
are used.
BEGIN
--
Job defined entirely by the CREATE JOB procedure.
DBMS_SCHEDULER.create_job (
job_name => 'test_full_job_definition',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN my_job_proc(''CREATE_PROGRAM
(BLOCK)''); END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=hourly; byminute=0',
end_date => NULL,
enabled => TRUE,
comments => 'Job defined entirely by the CREATE JOB
procedure.');
END;
/
BEGIN
--
Job defined by an existing program and schedule.
DBMS_SCHEDULER.create_job (
job_name => 'test_prog_sched_job_definition',
program_name => 'test_plsql_block_prog',
schedule_name => 'test_hourly_schedule',
enabled => TRUE,
comments => 'Job defined by an existing program and
schedule.');
END;
/
BEGIN
--
Job defined by an existing program and inline schedule.
DBMS_SCHEDULER.create_job (
job_name => 'test_prog_job_definition',
program_name => 'test_plsql_block_prog',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=hourly; byminute=0',
end_date => NULL,
enabled => TRUE,
comments => 'Job defined by existing program and inline
schedule.');
END;
/
BEGIN
--
Job defined by existing schedule and inline program.
DBMS_SCHEDULER.create_job (
job_name => 'test_sched_job_definition',
schedule_name => 'test_hourly_schedule',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN my_job_proc(''CREATE_PROGRAM (BLOCK)'');
END;',
enabled => TRUE,
comments => 'Job defined by existing schedule and inline
program.');
END;
/
For more details, see the book
Oracle Job Scheduling:
Creating robust task
management with dbms_job and Oracle 10g dbms_scheduler, by
Dr. Timothy Hall
|
and include the URL for the page.
