Oracle DBMS_SCHEDULER Part 1–The Basics

DBMS_SCHEDULER is Oracle’s full featured scheduling solution that runs inside the database. Think of it as a complete replacement for DBMS_JOB (which I do still use occasionally but am using less and less over time). DBMS_SCHEDULER has a lot of benefits over DBMS_JOB – ability to stop a job, ability to reuse code, ability to reuse schedules, ability to chain jobs into a job streams, etc.

This post is the first of a series of posts that I will be doing on DBMS_SCHEDULER. Actually I am doing a lot of Streams work again and it was kind of a toss up as to which tool I’d blog about first. Scheduler wins because I was doing some work with it this morning. ;-)

Today I will cover the very basic information you need to take advantage of DBMS_SCHEDULER. I’ll follow this up with posts going into more detail like showing code to build complex chains of jobs, rerunning failed jobs, running remote job, running external jobs, etc. Basically showing the abilities that make DBMS_SCHEDULER the preferred choice to writing a bunch of code yourself and using DBMS_JOB.

Version Info

The text and comments I am writing refer directly to 11gR2. That is the current release and the release you should be aiming for. I know plenty of people are still on 10g (including me at work) but if you are building new applications, you should be targeting 11g (as I am at work).

Documentation

As always, http://tahiti.oracle.com is the place for documentation. You will find valuable DBMS_SCHEDULER information in two documents. In the PL/SQL Packages and Types Reference you will find detailed information on all of the subprograms offered by DBMS_SCHEDULER. In the database Administrators Guide under Part IV Database Resource Management and Task Scheduling, Chapters 28-30, you will find detailed information about the usage of DBMS_SCHEDULER. I would recommend these chapters as MUST READ if you plan to take any kind of extensive use of scheduling in your application.

Security

Oracle has made DBMS_SCHEDULER as secure as any other database tool by designing it with security in mind. Scheduler objects are actual database objects and access/execution can be granted on those like any other objects. You will also need permissions (beyond execute on the DBMS_SCHEDULER package) to create scheduler objects.

Schedule Administrator

Some organizations have taken the approach that their will only be a single schedule administrator for any database (like Oracle Streams usually on has a single streams administrator per database). That means that only a single user can create jobs, schedules, chains, etc.

While I think it’s a good idea to only have a single streams administrator, I don’t think that’s a good idea for scheduling. I believe that an application owner (which no one would actually ever connect as in production) should have the permissions necessary to create the objects that belong to the application. It would kind of be like creating a table administrator and only allowing tables to be created in that schema. Other schemas would have code and they would all point to the same table schema. Just seems unwieldy and kind of silly to me.

For the rest of my posts on this topic, I am assuming that the application owner will also be the schedule administrator for that application and will have the required permissions to do the actions in these posts.There are two permissions that may be held a little tighter and I will address those below.

Permissions

There is a role called SCHEDULER_ADMIN in the database. This is a DBA role and should not be granted freely around the database. It’s always much better to grant the permissions that are actually needed. Having said that, if you are doing educational stuff in your own local database, go ahead and grant yourself SCHEDULER_ADMIN. It’s the easiest way to get started.

Before I list out the permissions to be granted, I will assume that if an application owner wants to create a program in a different schema, it will have the appropriate grants for that object (like execute on a stored procedure). The permissions I list here are for scheduler actions and objects only.

  • Create Job – This allows you to create most of the scheduler objects in a database including jobs, chains, schedules, programs, etc. As an owner of scheduler objects, you will want this permission.
  • Create Any Job – This is an administrative permission and gives you the same permissions as Create Job but it applies to any schema in the database except SYS. You don’t normally need this.
  • Create External Job – You need this if you will be creating jobs (or programs) that run in the OS instead of the Database. This includes remote executables.
  • Execute Any Program – This will allow you to schedule jobs (chains or programs) that are owned by others. Not normally needed but nice if you do need it. I’ve never needed it. If you application owner owns all the objects for an application, you won’t need it. DBAs will get this grant with the SCHEDULER_ADMIN role.
  • Execute Any Class – You can do resource management with job classes. I would grant this rather than individual job classes (which are explained below) unless you are truly doing resource management BY JOB CLASSES.
  • Manage Scheduler – This permissions allows you to manage some of the more esoteric scheduler objects like windows. You can also purge the scheduler logs. One grant that I think is in the wrong place is that this permission allows you to stop a job with the FORCE option. I think you should be able to force stop your own jobs and this grant should be for any job and should exist as a separate permission. But that’s just me.

 

  • Select on a scheduler object – If you want a different schema to see your objects, you can grant select on them (like GRANT SELECT ON my_group_of_stuff TO someuser;). Only works on groups of objects (explained below).
  • Execute on a scheduler object – If you want to allow someone else to use one of your scheduler objects in their job, you can grant execute on that object.
  • Alter on scheduler objects – Allow a different schema to make some limited changes (as well as see) your scheduler objects
  • ALL on a scheduler objects – Gives everything that can be given for that type of scheduler object to a different schema

Scheduler Object Types

DBMS_SCHEDULER works with a variety of object types. You will find yourself using some more often than others (and some you will never touch). Here is a list of the object types.

  • PROGRAM – it all starts with the program. A program is a PL/SLQ procedure (can be packaged but not a function), PL/SQL block or external OS program.
  • JOB – If a program is the physical thing that runs, a JOB is the logical thing that will run, It includes the program, when to run it (now or later) and where to run it (here locally or on a remote system).
  • SCHEDULE – An event or a calendar of when you want something to run. Schedules can be very simple or very complex. Schedules will get a blog post.
  • CHAINS – You can chain jobs together into a job stream and put dependencies between them. Very powerful for complex operations. Each job defined in a chain is a CHAIN STEP and you can build complex rules for execution and exception handling.
  • FILE WATCHERS – A file watcher watches for a file. Go figure. It can look for a file on the local file system or look for a file on any system running the scheduler agent.
  • CREDENTIALS – Meta data that stores a user name and password (hashed) for a database (remote) or an OS user (local or remote) for external programs.
  • DESTINATION – Either a remote database name or a remote server.
  • GROUPS – A group of other scheduler objects so that it is easier to refer to them. Some scheduler procs allow you to send in a list of objects. You can create a group for those if you do it often. Also used for granting SELECT on objects.
  • JOB CLASSES – These are used for job prioritization and resource management. Can also be used for instance affinity under RAC.
  • WINDOWS – A window allows you to identify a time of day (or week, month, year, etc) where resource allocation may change. You may give higher resource usage between midnight and 5am that you would during business hours. This is fairly esoteric and, if used, will be defined at a global level (by that I mean data center level) and will be set and enforced globally, not by an application.

So that’s it for the basics. In my next post on this topic, I will show you how to programmatically create programs and jobs and how to use SQL Developer to do the same. I’ll follow that one up with a complex example of job chains and dependencies.

Take care,

LewisC

You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.

1 Comment »