Book Giveaway & Chapter Download: Mastering Oracle Scheduler in Oracle 11g Databases

DZone and Packt Publishing have partnered to bring you this exclusive chapter from 'Mastering Oracle Scheduler in Oracle 11g Databases' (by Ronald Rood). This chapter explains how to make good use of the Scheduler and apply job separation.

Control the Scheduler

Oracle Scheduler does a lot of out of the box things, and if the demands are not too high, Oracle Scheduler can cover most of the situations. When your application has to run many thousands of jobs in an hour and log them over a long period, a little more attention is needed. In cases where the demands are high, we need to take care of a few more things to keep the system happy. What we need to do depends on how the Scheduler is used and what kind of load it is supposed to handle.

In this chapter, we will take a closer look at how we can control this beast. We will take a look at the privileges for job creation, job execution, and Scheduler management. We will also examine how to control logging retention and find a way to prevent jobs from running when the database starts.

Like most object types in the database, as seen at the beginning of Chapter 1, Oracle enables us to create Scheduler objects. The privileges create job and create external job are very important. They should normally be used when building an application system . Also, there is a create any job privilege, which can be useful when you need to create a job in a different schema. Normally, this privilege should not be granted to anyone. It will allow the grantee to run an arbitrary code on any schema, which is not particularly desirable. Instead, just log on to the correct schema and perform the tasks using the correct privileges. 

(A priviledges chart is shown here in the download below).

Here, it is interesting to note that the drop any job privilege seems to be missing. For other object types, we have the create any privilege and also the drop any privilege. Making use of these any privileges looks smart at first glance. However, it makes a system less transparent and more difficult to maintain eventually. Preferably, objects are granted explicitly instead of falling back on any privileges when building an application. For example, select any table and you will observe that it is much harder to find how an application flows or what the impact of dropping an object is. If the privileges are explicitly granted, we can see that someone is using our object.

Mostly, it is smarter to create schemas using the least privileges principle. This means that one or more schemas contain tables that hold the data, and other schemas contain the procedures that act on the various tables. Users or roles should have privileges on the procedures.

The DBMS_SCHEDULER package is available to the public. The use of the package is controlled by the underlying privileges such as create jobs, manage scheduler, and the execution privileges on the products of the DBMS_SCHEDULER package such as jobs, programs, and job classes.

You can buy 'Mastering Oracle Scheduler in Oracle 11g Databases' from the Packt Publishing website. Packt offers free shipping to the US, UK, Europe, and selected Asian countries. Alternatively, you can buy the book from Amazon, BN.com, Computer Manuals and most other Internet book retailers.