Advanced Queueing offers great queueing functionality built into Oracle Database. When you want to create a queue, Oracle Database will create several supporting objects. Depending on how you use Advanced Queueing and Oracle Database, these objects might change.
Let’s investigate a case that came up during a project.
After I recreated a queue table, some of the underlying objects changed definition. Specifically, columns that apparently contained user information changed from a NUMBER to a VARCHAR2.
|Object Name||Column Name||Data Type Before||Data Type After|
The column data type changed from NUMBER to VARCHAR2. I created the queue tables using
DBMS_AQADM and Oracle Database created the AQ$ objects recursively.
Is this something to be worried about?
Why Does the Data Type Change
Advanced Queueing has been around for a while and it has evolved. To control the behavior of Advanced Queueing, you can use the
compatible parameter when you create queue tables.
In Oracle Database 19c, you can set
compatible parameter on a queue table to one of the following:
When you create a queue table, it is an optional parameter:
SQL> begin dbms_aqadm.create_queue_table ( ... compatible => '10.0' ); end;
If you don’t explicitly specify a
compatible setting, it is derived from the database instance parameter
You can find the
compatible setting of a queue table using:
SQL> select queue_table, compatible from user_queue_tables;
In the documentation, you can find information on which functionality gets enabled by which
compatible setting. In this case, the following is of interest:
Mixed case (upper and lower case together) queue names, queue table names, and subscriber names are supported if database compatibility is 10.0
When you set
compatible on the queue table to 10.0 there is better support for certain user names (subscriber names), and that requires a different data type on the underlying objects.
How to Solve the Problem
There are two options:
- You can recreate the queue tables using the same
compatiblesetting. You start by querying
USER_QUEUE_TABLESto find the
compatiblesetting. Then, use
DBMS_AQADM.CREATE_QUEUE_TABLEto recreate the queue and remember to specify the correct
- You can adapt the newest
compatiblesetting on your queues. The underlying objects change. You can use all the features of Advanced Queueing. You should test your application and ensure it works with the new setting.
I recommend option 2. It is uses the default setting for
compatible. The default setting has been around in many years, so it is thoroughly tested and I assume that most customers use this configuration.
You Can Migrate Old Queue Tables
You can migrate old queues in your Oracle Database. Any queues that don’t have
compatible set to 10.0, you can migrate to the newest
SQL> begin dbms_aqadm.migrate_queue_table(..., compatible => '10.0.0'); end;
Now you can start to use all the features in Advanced Queueing.
You can query the data dictionary to find old queues in your Oracle Database:
SQL> select queue_table, compatible from user_queue_tables where compatible != '10.0.0';
Thanks to oracle-base.com for supplying the starting point for my test case.
conn / as sysdba --create user and grant privileges drop user appuser cascade; create user appuser identified by appuser; grant dba to appuser; conn appuser/appuser --type used for queue payload create type car_type as object ( name varchar2(20), color varchar2(10) ); / --get the database instance compatible setting --used to derive the queue table compatible setting --if not specified select value from v$parameter where name='compatible'; begin --create queue table without expliciti 'compatible' --compatible should be 10.0.0 dbms_aqadm.create_queue_table ( queue_table => 'APPUSER.CAR_QUEUE_TAB', queue_payload_type => 'APPUSER.CAR_TYPE'); --create new queue table with lower compatible setting dbms_aqadm.create_queue_table ( queue_table => 'APPUSER.CAR_QUEUE_TAB8', queue_payload_type => 'APPUSER.CAR_TYPE', compatible => '8.0'); end; / --verify queue table compatible setting select queue_table, compatible from user_queue_tables;