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.
The Situation
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 |
---|---|---|---|
AQ$<queue_table> | ENQ_USER_ID | NUMBER | VARCHAR2 |
AQ$<queue_table> | DEQ_USER_ID | NUMBER | VARCHAR2 |
AQ$_<queue_table>_F | ENQ_UID | NUMBER | VARCHAR2 |
AQ$_<queue_table>_F | DEQ_UID | NUMBER | VARCHAR2 |
AQ$_<queue_table>_H | DEQUEUE_USER | NUMBER | VARCHAR2 |
AQ$_<queue_table>_L | DEQUEUE_USER | NUMBER | VARCHAR2 |
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:
- 8.0
- 8.1
- 10.0
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 compatible
.
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
compatible
setting. You start by queryingUSER_QUEUE_TABLES
to find thecompatible
setting. Then, useDBMS_AQADM.CREATE_QUEUE_TABLE
to recreate the queue and remember to specify the correctcompatible
setting. - You can adapt the newest
compatible
setting 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 compatible
setting:
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';
Appendix
Thanks to oracle-base.com for supplying the starting point for my test case.
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;