Why Does User ID Columns Change Data Type When You Recreate a Queue Table

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

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'

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:

  1. You can recreate the queue tables using the same compatible setting. You start by querying USER_QUEUE_TABLES to find the compatible setting. Then, use DBMS_AQADM.CREATE_QUEUE_TABLE to recreate the queue and remember to specify the correct compatible setting.
  2. 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');

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.

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';

   --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');

--verify queue table compatible setting
select queue_table, compatible from user_queue_tables;

Changing COMPATIBLE Parameter and Data Guard

When you upgrade your Oracle Database, you should also decide how to raise the COMPATIBLE parameter. The considerations are the same when you use Data Guard, but the procedure is different.


The main reason for raising COMPATIBLE is to enable new features.

Some new features are not backward compatible, e.g., blockchain tables. When someone introduces such features, the database:

  • is no longer backward compatible
  • can no longer be downgraded

When you upgrade, COMPATIBLE stays at the old setting. You need to actively raise it and allow the use of these new features. You are in total control.

Here is a video with more details about COMPATIBLE.


I recommend that you raise COMPATIBLE one or two weeks after the upgrade.

  • When you have seen that the new release works fine in production.
  • When you are confident that a database downgrade won’t be needed.

Raising COMPATIBLE requires a database restart, i.e., an outage. If such is unacceptable, you must raise COMPATIBLE as part of the upgrade. But be advised, it severely limits your fallback options.

AutoUpgrade does not change COMPATIBLE, unless you explicitly state it in the config file:



I recommend that you always set COMPATIBLE to the default of a database release:

  • 19.0.0
  • 21.0.0

If you only have non-CDBs then it might not matter much. But in a multitenant environment, having a uniform, COMPATIBLE setting is very beneficial. This allows PDBs to move between CDBs without problems.


You need to raise COMPATIBLE on all databases in your Data Guard configuration. The order is important:

  • First, standby databases (individual order is not important)
    STANDBY SQL> --Ensure redo apply is running
    STANDBY SQL> alter database recover managed standby database disconnect from session;
    STANDBY SQL> alter system set compatible='19.0.0' scope=spfile sid='*';
    STANDBY SQL> alter database recover managed standby database cancel;
    [oracle@standby]$ srvctl stop database -d $ORACLE_UNQNAME
    [oracle@standby]$ srvctl start database -d $ORACLE_UNQNAME -o mount
    STANDBY SQL> alter database recover managed standby database disconnect from session;
  • Last, primary database
    PRIMARY SQL> alter system set compatible='19.0.0' scope=spfile sid='*';
    [oracle@primary]$ srvctl stop database -d $ORACLE_UNQNAME
    [oracle@primary]$ srvctl start database -d $ORACLE_UNQNAME

Other Blog Posts in This Series

Further Reading