Oracle PL/SQL for DBAs

Owner of the Schedule

Another important column in the output from the DBA_SCHEDULER_JOBS view is SCHEDULE_OWNER. In this case, it shows ARUP, the user that created the schedule. The implication of this column is significant: a schedule need not be created by the user who actually runs a job; it could be defined by another user. Note, however, that the user who creates a schedule must have the CREATE JOB system privilege . Because the schedule is created using the DBMS_SCHEDULER package , that user must also have the EXECUTE privilege on the package.

Suppose that I am creating a single user named SCHED_MANAGER, to manage all of the schedules for your database. Doing so will make it easier to manage schedules and will establish a single point of control for these schedules. As I noted above, that user must be given the following privileges:

CREATE JOB

EXECUTE ON DBMS_SCHEDULER

My job creation code now looks like this.

      1  BEGIN
      2     DMS_SCHEDULER.create_job
      3           (job_name           => 'tabstat_savings',
      4            job_type           => 'stored_procedure',
      5            job_action         => 'collect_stats_checking',
    
      6            schedule_name      => 'SCHED_MANAGER.opt_stat_coll_sched',
      7            enabled            => TRUE,
      8            comments           => 'Collect SAVINGS Stats'
      9            );
     10* END;

Note that line 6 has changed. Now the schedule_name parameter shows SCHED_MANAGER.opt_stat_coll_sched, which indicates the owner of the schedule. In my earlier examples, I did not include a prefix for the schedule name; if you omit the prefix, the default is that the schedule belongs to the user who is ...