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.