 |
|
Oracle Scheduling Scheduler Privileges
Oracle Tips by Burleson Consulting |
Scheduler Privileges
Table 2.1 shows the system privileges
associated with the scheduler.
SYSTEM
PRIVILEGE |
PURPOSE |
CREATE JOB |
Enables the
user to create jobs, schedules and programs in their own
schema. The user can always alter and drop jobs, schedules and
programs which they own, even when they do not have the CREATE
JOB privilege. |
CREATE ANY
JOB |
Enables the
user to create jobs, schedules, and programs in any schema.
This effectively gives the grantee the ability to run code as
any user so it must be issued with care. |
EXECUTE ANY
PROGRAM |
Enables jobs
the ability to use programs from any schema. |
EXECUTE ANY
CLASS |
Enables jobs
to run under any job class. |
MANAGE
SCHEDULER |
Enables the
user to create, alter and drop job classes, windows and window
groups. It also enables the user to purge scheduler logs and
modify scheduler attributes. |
Table 2.1 - System privileges associated with
the scheduler.
Table 2.2 shows the object privileges
associated with the scheduler.
OBJECT PRIVILEGE |
PURPOSE |
EXECUTE |
This can only be granted for programs and
job classes, enabling jobs to use the granted objects. |
ALTER |
Enables the user to alter or drop the
object it is granted on. The alter operations vary depending on
the object. |
ALL |
The can be granted on jobs, programs,
schedules and job classes. It enables the user to perform all
possible operations on the object it is granted on. |
Table 2.2 - The object privileges associated
with the scheduler.
The SCHEDULER_ADMIN role has all the above
privileges granted to it with the ADMIN option, making it extremely
powerful. This role is granted in turn to the DBA with the
ADMIN option.
Programs
The create_program procedure is used to store
metadata about a task, but it stores no schedule information.
PROCEDURE
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)
The parameters associated with this procedure
and their usage are as follows:
* program_name - A name that uniquely
identifies the program. The program name can include a schema
qualifier.
* program_type - The type of action associated
with this program (PLSQL_BLOCK, STORED_PROCEDURE or EXECUTABLE).
* program_action - The actual work that is done
by the program.
* number_of_arguments - The number of arguments
required by this program. Programs using arguments must have
their arguments defined before they can be enabled.
* enabled - A flag which indicates if the
program is enabled or not. If the program accepts arguments,
it cannot be enabled until the arguments are defined.
* comments - Free text, allowing the user to
record additional information.
The action of a program may be a PL/SQL block,
a stored procedure or an OS executable file. The following
examples show how each type of program is defined.
BEGIN
-- PL/SQL Block.
DBMS_SCHEDULER.create_program (
program_name => 'test_plsql_block_prog',
program_type => 'PLSQL_BLOCK',
program_action => 'BEGIN my_job_proc(''CREATE_PROGRAM
(BLOCK)''); END;',
enabled => TRUE,
comments => 'CREATE_PROGRAM
test using a PL/SQL block.');
END;
/
BEGIN
-- Stored Procedure with Arguments.
DBMS_SCHEDULER.create_program (
program_name => 'test_stored_procedure_prog',
program_type => 'STORED_PROCEDURE',
program_action => 'my_job_proc',
number_of_arguments => 1,
enabled
=> FALSE,
comments => 'CREATE_PROGRAM test using a
procedure.');
DBMS_SCHEDULER.define_program_argument (
program_name
=> 'test_stored_procedure_prog',
argument_name => 'p_text',
argument_position => 1,
argument_type => 'VARCHAR2',
default_value => 'This is a default value.');
DBMS_SCHEDULER.enable (name => 'test_stored_procedure_prog');
END;
/
BEGIN
-- Shell Script (OS executable file).
DBMS_SCHEDULER.create_program (
program_name => 'test_executable_prog',
program_type =>
'EXECUTABLE',
program_action =>
'/u01/app/oracle/dba/MyJob.ksh',
number_of_arguments => 0,
enabled
=> TRUE,
comments
=> 'CREATE_PROGRAM test using a schell script.');
END;
/
Programs that accept arguments must have their
arguments defined before they can be enabled. Arguments are
defined, manipulated and dropped using the define_program_argument,
define_metadata_argument, and drop_program_argument procedures,
whose call specifications are listed below.
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)
PROCEDURE
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)
PROCEDURE
define_metadata_argument(
program_name
IN VARCHAR2,
metadata_attribute IN VARCHAR2,
argument_position IN PLS_INTEGER,
argument_name
IN VARCHAR2 DEFAULT NULL)
PROCEDURE
drop_program_argument (
program_name
IN VARCHAR2,
argument_position IN PLS_INTEGER)
PROCEDURE drop_program_argument (
program_name
IN VARCHAR2,
argument_name
IN VARCHAR2)
The important parameters associated with these
procedures and their usage are noted as follows:
* program_name - A name that uniquely
identifies the program.
* argument_position - The position of the
argument in the call specification.
* argument_name - The name of the argument.
* argument_type - The datatype of the argument.
* default_value - The argument value used if no
specific value is assigned via the job.
* out_argument - A flag that indicates the
direction of the argument.
Programs can be created using the Create
Program screen of the OEM 10g DB Control as shown in Figure 2.4.
Figure 2.4 ? OEM DB Control: Create Program
Information about programs can be displayed
using the dba_scheduler_programs view. The following script
uses this view to display basic information about the currently
defined programs.
*
programs.sql
set verify
off
select
owner,
program_name,
enabled
from
dba_scheduler_programs
where
owner = decode(upper('&1'), 'ALL', owner, upper('&1'))
;
The programs.sql script can display all
programs or only those programs of a specified user.
SQL>
@programs all
OWNER
PROGRAM_NAME
ENABL
------------------------------ ------------------------------ -----
SYS
PURGE_LOG_PROG
TRUE
SYS
GATHER_STATS_PROG
TRUE
JOB_USER
TEST_PLSQL_BLOCK_PROG
TRUE
JOB_USER
TEST_STORED_PROCEDURE_PROG TRUE
JOB_USER
TEST_EXECUTABLE_PROG
TRUE
5 rows
selected.
SQL>
@programs job_user
OWNER
PROGRAM_NAME
ENABL
------------------------------ ------------------------------ -----
JOB_USER
TEST_PLSQL_BLOCK_PROG
TRUE
JOB_USER
TEST_STORED_PROCEDURE_PROG TRUE
JOB_USER
TEST_EXECUTABLE_PROG
TRUE
3 rows
selected.
Information about program arguments can be
displayed using the dba_scheduler_program_args view. The
following script uses this view to display information about the
arguments of currently defined programs.
*
program_args.sql
--
*************************************************
-- Copyright ? 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
-- Parameters:
-- 1) Specific USERNAME or ALL which doesn't limit
output.
-- 2) Program name.
-- *****************************************************************
set verify
off
column argument_name format a20
column default_value format a30
select
argument_position,
argument_name,
default_value
from
dba_scheduler_program_args
where
owner = decode(upper('&1'), 'ALL', owner, upper('&1'))
and
program_name = upper('&2')
;
The output
from the program_args.sql script is displayed below.
SQL> @program_args
job_user test_stored_procedure_prog
ARGUMENT_POSITION ARGUMENT_NAME
DEFAULT_VALUE
----------------- -------------------- -----------------------------
1 P_TEXT This is a default
value.
1 row
selected.
Programs that are no longer used can be removed
using the drop_program procedure, whose call specification is listed
below.
PROCEDURE
drop_program (
program_name
IN VARCHAR2,
force IN BOOLEAN
DEFAULT FALSE)
The parameters associated with this procedure
and their usage are as follows.
* program_name - A name that uniquely
identifies the program.
* force - When set to TRUE, all jobs which
reference the program are disabled prior to the program being
dropped. If set to FALSE and jobs reference the program, an
error is produced. In addition, all program arguments
information is dropped.
The following examples show how the
drop_program procedure is used.
BEGIN
DBMS_SCHEDULER.drop_program (program_name => 'test_plsql_block_prog');
DBMS_SCHEDULER.drop_program (program_name => 'test_stored_procedure_prog');
DBMS_SCHEDULER.drop_program (program_name => 'test_executable_prog');
END;
/
One can determine that the programs have been
removed by checking the output of the programs.sql script.
SQL>
@programs all
OWNER
PROGRAM_NAME
ENABL
------------------------------ ------------------------------ -----
SYS
PURGE_LOG_PROG
TRUE
SYS
GATHER_STATS_PROG
TRUE
2 rows
selected.
Program information is also available from the
OEM 10g DB Control via the Scheduler Programs screen shown in Figure
2.5.
Figure 2.5 ? OEM 10g DB Control: Scheduler
Programs
Now that defining reusable programs has been
explained, the next section will explain the defining of reusable
schedules.
 |
This is an excerpt from the book "Oracle
Job Scheduling" by Dr. Tim Hall. You can buy it direct
from the publisher for 30%-off and get instant access to the
code depot of Oracle job scheduling scripts. |