Skip to Main Content
Oracle PL/SQL for DBAs
book

Oracle PL/SQL for DBAs

by Arup Nanda, Steven Feuerstein
October 2005
Intermediate to advanced content levelIntermediate to advanced
454 pages
14h 44m
English
O'Reilly Media, Inc.
Content preview from 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 ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle PL/SQL Best Practices

Oracle PL/SQL Best Practices

Steven Feuerstein
Expert Oracle PL/SQL

Expert Oracle PL/SQL

Ron Hardman, Michael McLaughlin
Oracle PL/SQL For Dummies

Oracle PL/SQL For Dummies

Michael Rosenblum, Paul Dorsey

Publisher Resources

ISBN: 0596005873Supplemental ContentErrata Page