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.
Job Creation
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 privileges 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.
Click here to download the entire chapter.
If you wish to read more about the book click here: http://www. packtpub. com/mastering-oracle-scheduler-in-oracle-11g-databases/book
Ronald RoodRonald Rood has been an IT professional for over 20 years. His roots are in the Bull gcos8 mainframes where he played with assembly language until Oracle and UNIX came across his path. He eagerly joined the Oracle community and became a skilled innovating DBA and trouble shooter. Ronald’s real power is in the combination of the rich UNIX world and Oracle. There is no such thing as a problem that can not be solved; it just might take a little time. Next to C, PRO*C, lots of scripting languages and ? of course ? PL/SQL, he also speaks Dutch, English, German, and some French. In his private time that he shares with his family with two children he likes to take an occasional dive (from the sky), fly radio controlled models, ride recumbent, and work as a volunteer for a local Water Scouts group.