Tutorials Oracle SchedulerOn many occasions, a calendar will do fine for scheduling jobs. However, there are situations that require an immediate action and which cannot wait for the next activation based on a calendar. An example might be of a user who logs on to the database and then, using a logon trigger, more actions are executed.
Another example could be a situation in which we want a backup server to be utilized to the maximum, but not beyond that. We schedule all the backups independent of each other and have each backup raise an event when ready, which tells the system that another backup can go ahead. By letting the backup jobs wait for an event that essentially flags "there is backup capacity available now", we make sure that a backup does not take longer than needed. We also make sure that the backup system is pushed to the highest throughput.
When we just use a preset date and time to start the backups, chances are that more backups are running at the same time (possibly caused by the growth of one or more databases, which is potentially causing their backups to be longer than anticipated). On the other hand, when we make sure that more backups are never ever run at the same time, we will likely have lots of idle time in the backup system.
This is a reason enough to learn how we can make good use of events. However, there are a few things we need to do. It essentially comes down to:
• Creating a queue and defining a payload for that queue
• Having a process that puts the message on the queue
• Coupling one or more job definition(s) to the queue
Again, this gives a kind of control that is hard to find in third-party scheduling packages.
Event messages are placed on an event queue and this is handled by AQ. So we need to call the AQ packages and for that we require DBMS_AQ and DBMS_AQADM. In the days before Oracle 10g, we needed to set the AQ_TM_PROCESSES parameter to a non-zero value to work. Since Oracle 10g, this is no longer the case and we can leave the AQ_TM_PROCESSES value to zero.
First, make sure we can use AQ.
The expected output is as shown in the following screenshot:
If this query does not show MARVIN having the EXECUTE privileges on both DBMS_AQ and DBMS_AQADM, we need to give them to our user.
As a DBA, execute the following:
This makes sure that marvin has enough privileges to be able to create and use queues. Now connect as marvin, create an object type that we can use to put a message on the queue, and read from the queue later on.
This defines a type consisting of one msg field of 20-character length. This is the type we will be using in the queue for which we create a queue table next:
This creates a queue table called bckup_qt, which contains messages defined by bckup_msgt. After that, bckup_q starts immediately.
