Oracle Advanced Queueing

What is Advanced Queuing ?

The concept of advanced queuing is such that a message can be queued up by a single process such that one or more process can read the message. Error messages can be returned and the order of job execution from a queue is controllable. Message queues store queued messages. There can be many message queues present in a database. The two packages DBMS_AQADM and DBMS_AQ can be used to administer message queues. Queues provide time-based management.

Job Queue Elements

The Queue Processes

The queue processes are known as the time manager. These processes include expiration, delay and retention processes. Set the AQ_TM_PROCESSES parameter in the init.ora file to 1. With message propogation set the job_queue_processes parameter to greater than 0 to allow a single message to be distributed to multiple databases and multiple queues. Advanced queuing requires the Objects option. Queue management requires the AQ_ADMINISTRATOR role.

Job Queues

Job queues allow the scheduling of batch jobs which execute Oracle objects such as PL/SQL procedures against an Oracle database. Full scheduling capability is available with scheduled execution times plus frequency repetitions. Note that scheduled jobs will not execute in restricted mode. There can be upto 36 scheduled jobs running in a database at once. The JOB_QUEUE_PROCESSES and JOB_QUEUE_INTERVAL parameters control the number of a frequency of schedule polling respectively. Job queues are manually managed using the DBMS_JOB package.

DBMS_JOB Package