There are five basic steps involved in using Oracle AQ:
In many organizations, steps 1 through 3 will be performed by a database administrator. They require specific authorization and can be a complex process, depending on the type of queues you want to create. The fourth and fifth steps will be performed from within the PL/SQL programs that you have written to take advantage of this message queuing technology.
Steps 3, 4, and 5 are explained in detail later in this chapter. For now, let's take a look at the steps you must take to install AQ and to grant the proper privileges to Oracle accounts in order to perform AQ activities.
The DBMS_AQ and DBMS_AQADM packages (and the database object used by them) are created when the Oracle database is installed. You should not have to take any special steps to install Oracle AQ and make it available in your environment. (You will, as covered in a later section, need to grant access to Oracle AQ to specific users.)
Before you can execute any programs in the DBMS_AQADM or DBMS_AQ packages, you may need to grant EXECUTE privilege on those packages explicitly (role-based privileges do not take effect inside stored code). If you have trouble executing any program in these packages, connect to the SYS account and execute these commands,
GRANT EXECUTE ON DBMS_AQADM TO <user>; GRANT EXECUTE ON DBMS_AQ TO <user>;
where <user> is the name of the account to which you want to grant EXECUTE privilege.
You will need to set one or more initialization parameters in order to obtain the desired behavior from Oracle AQ.
184.108.40.206 Starting the Queue Monitor
One of the features of Oracle AQ is the ability to manage the time in which messages are available for dequeueing and the time after which messages are "expired." If you want to employ this "time management" feature, you need to add a parameter to your initialization file or INIT.ORA file for your database instance. The name of this parameter is AQ_TM_PROCESSES, and it can be assigned a nonzero integer value. If the parameter is set to any number between 1 and 10, that number of Queue Monitor background processes will be created to monitor messages in the various queues.
The name of the process created is,
where oracle_SID is the System ID for the database instance being started. If the parameter is not specified or is set to 0, then the Queue Monitor background process will not be created.
Here is an example of a line in the INIT.ORA file that specifies that one Queue Monitor process be created:
AQ_TM_PROCESSES = 1
If the Queue Monitor process it not started, you will not be able to start and stop the Queue Monitor using the DBMS_AQADM.START_TIME_MANAGER and DBMS_AQADM.STOP_TIME_MANAGER procedures, respectively.
Message propagation (an Oracle 8.0.4 AQ feature) is implemented by job queue processes. The number of these processes is defined with the JOB_QUEUE_PROCESSES parameter. The default value for this parameter is 0. If you want message propagation to take place, you must set this parameter to at least 1. If you plan to propagate messages from many queues in your instance or receive messages to many destination queues, you (or your DBA) should set this parameter to a higher value.
Here is an example of a setting of this parameter to three processes:
JOB_QUEUE_PROCESSES = 3
See Chapter 12, Managing Server Resources , for more information about DBMS_JOB and the setting of this parameter.
COMPATIBLE = 8.0.4
This parameter will be checked under any of the following conditions:
You can also downgrade to 8.0.3 after you have used the Oracle 8.0.4 features by using the following command:
ALTER DATABASE RESET COMPATIBILITY
Users will not be able to restart the database in 8.0.3 compatibility mode under the following conditions:
If you have been using Oracle AQ in Oracle 8.0.3 and are now upgrading to 8.0.4, check the online Oracle documentation for upgrade steps you must take.
When working with AQ, you will perform either administrative or operational activities. Administrative tasks include creating queue tables and queues, and starting and stopping queues. Operational tasks include different aspects of using existing queues (i.e., queuing messages to them and dequeuing messages from them). Access to these operations is granted to users through database roles. There are two such roles:
Here is an example of the steps you might perform from SQL*Plus to set up an AQ administrator:
SQL> CONNECT SYS/CHANGE_ON_INSTALL SQL> GRANT AQ_ADMINISTRATOR_ROLE TO AQADMIN;
The AQADMIN account can now set up that old standby SCOTT as an AQ user account as follows:
SQL> CONNECT aqadmin/top_secret SQL> GRANT AQ_USER_ROLE TO scott;
If you further wish to create and manipulate queue tables that are enabled for multiple dequeuing (in other words, queues that use subscriber lists to dequeue a message to multiple consumers), you must also execute the GRANT_TYPE_ACCESS procedure of the DBMS_AQADM package. The SYS account must do this for the AQ administrator account; that administrator can then do the same for AQ user accounts. Here are the steps:
1. Enable AQADMIN for multiple dequeues from SYS:
SQL> CONNECT SYS/CHANGE_ON_INSTALL SQL> exec DBMS_AQADM.GRANT_TYPE_ACCESS ('aqadmin');
2. Enable SCOTT for multiple dequeues from AQADMIN:
SQL> CONNECT aqadmin/top_secret SQL> exec DBMS_AQADM.GRANT_TYPE_ACCESS ('scott');
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.