Tag: queue table

How to Export/Import A Single Queue Using Data Pump

In a recent project, a customer used Advanced Queueing (AQ), and I had to move a single queue from one database to another.

In Oracle AI Database, you can use Data Pump to move queues. According to the documentation, exporting and importing a queue is straightforward, but there are some caveats to be aware of.

How to Export a Queue

I want to export the ORDER_Q queue in the APPUSER schema.

  1. Under the hood, a queue consists of a queue table. Often, the queue uses an object type for its payload. I start by finding the queue table name and payload type:

    select q.queue_table, qt.object_type
from   dba_queues q, dba_queue_tables qt 
where  q.owner='APPUSER' and q.name='ORDER_Q'
and    q.owner=qt.owner and q.queue_table =qt.queue_table;

   QUEUE_TABLE        OBJECT_TYPE
______________ __________________
ORDER_QT       APPUSER.ORDER_T

  2. I prepare a Data Pump parfile named queue_table_export.par. It exports the queue table:

    directory=dpdir
dumpfile=order_qt.dmp
logfile=order_q_export.log
tables=APPUSER.ORDER_QT
exclude=statistics
    • The tables parameter is the queue table for the queue I want to export. I find it using the query above.

  3. Then, I export the queue table:

    expdp ... parfile=queue_table_export.par

  4. Next, I create a new parfile named payload_type_export.par. It exports the object_type or payload_type:

    directory=dpdir
dumpfile=queue_types.dmp
logfile=queue_types_export.log
schemas=appuser
include=type:"IN ('ORDER_T')"
    • To export types, I must perform a schema export.
    • I use the include parameter to include only types and further limit the export to the ORDER_T type.

  5. Then, I export the type:

    expdp ... parfile=payload_type_export.par

How to Import

  1. I start by importing the type:

    impdp ... dumpfile=queue_types.dmp

  2. Then, I import the queue table:

    impdp ... dumpfile=order_qt.dmp
    • Data Pump imports the queue table and all messages.
    • It also calls the AQ API to create the queue infrastructure.

  3. I connect to the database and start the queue:

    BEGIN
DBMS_AQADM.START_QUEUE(
	queue_name => 'APPUSER.ORDER_Q'
);
END;
/
    • Note that I start the queue by its name ORDER_Q. It was created automatically during the import of the queue table.

  4. That’s it. I’ve moved the queue and can start using it.

What Happens During Import

Let’s see how Data Pump uses the AQ API when it imports the queue table.

  1. I start Data Pump to extract the DDL from the dump file:
    impdp ... dumpfile=order_qt.dmp sqlfile=order_qt.sql
    • Data Pump reads the dump file and extracts the statements needed to perform this import.
    • It doesn’t perform the import.
    • The statements are written to the text file specified by sqlfile.
  2. I examine the text file. This is what Data Pump would do during an import.
    • Data Pump starts by creating the queue table:
      CREATE TABLE "APPUSER"."ORDER_QT"
   (    "Q_NAME" VARCHAR2(128 BYTE),
        "MSGID" RAW(16),
        "CORRID" VARCHAR2(128 BYTE),
        "PRIORITY" NUMBER,
        ...
    • Next come a few indexes:
      CREATE INDEX "APPUSER"."AQ$_ORDER_QT_I"
   ON "APPUSER"."ORDER_QT" ("Q_NAME", "STATE", "ENQ_TIME", "STEP_NO", "CHAIN_NO", "LOCAL_ORDER_NO")
   ...
    • Finally, the call to the AQ API to create the queue:
      BEGIN
SYS.DBMS_AQ_IMP_INTERNAL.IMPORT_QUEUE_TABLE('ORDER_QT',1,16801800,2,0,0,'', SYS.DBMS_AQ_IMP_INTERNAL.DBVER_10i, '00:00');
COMMIT;
END;
/

BEGIN
SYS.DBMS_AQ_IMP_INTERNAL.IMPORT_QUEUE(HEXTORAW('598D4E9609226B60E0635301000A0A08'),'ORDER_QT','AQ$_ORDER_QT_E',1,0,0,0,0,'exception queue');
COMMIT;
END;
/

BEGIN
SYS.DBMS_AQ_IMP_INTERNAL.IMPORT_QUEUE(HEXTORAW('598D4E9609236B60E0635301000A0A08'),'ORDER_QT','ORDER_Q',0,5,0,0,0,'');
COMMIT;
END;
/
    • You can’t see the rows being loaded into the queue, but that happens too. All messages in the queue are also exported.
  • The DBMS_AQ_IMP_INTERNAL is an internal API. Don’t call it yourself.

What About a Full or Schema Export

If you perform a full or schema mode export, it’s simple. Data Pump exports all the required objects, including the types.

The only thing you must do is to start the queues after the import.

What If

The payload type must exist when you import the queue table. If you try to import the queue table without the type, Data Pump throws an error:

Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39117: Type needed to create table is not included in this operation. Failing sql is:
CREATE TABLE "APPUSER"."ORDER_QT" ("Q_NAME" VARCHAR2(128 BYTE), "MSGID" RAW(16), "CORRID" VARCHAR2(128 BYTE), "PRIORITY" NUMBER, "STATE" NUMBER, "DELAY" TIMESTAMP (6), "EXPIRATION" NUMBER, "TIME_MANAGER_INFO" TIMESTAMP (6), "LOCAL_ORDER_NO" NUMBER, "CHAIN_NO" NUMBER, "CSCN" NUMBER, "DSCN" NUMBER, "ENQ_TIME" TIMESTAMP (6), "ENQ_UID" VARCHAR2(128 BYTE), "ENQ_TID" VARCHAR2(30 BYTE), "DEQ_TIME" TIMESTAMP (6), "DEQ_UID" VARCHAR2(128 BYTE), "DEQ_TID" VARCHAR2(30 BYTE), "RETRY_COUNT" NUMBER, "EXCEPTION_QSCHEMA" VARCHAR2(128 BYTE), "EXCEPTION_QUEUE" VARCHAR2(128 BYTE), "STEP_NO" NUMBER, "RECIPIENT_KEY" NUMBER, "DEQUEUE_MSGID" RAW(16), "SENDER_NAME" VARCHAR2(128 BYTE), "SENDER_ADDRESS" VARCHAR2(1024 BYTE), "SENDER_PROTOCOL" NUMBER, "USER_DATA" "APPUSER"."ORDER_T" , "USER_PROP" "SYS"."ANYDATA" ) USAGE QUEUE SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS"  OPAQUE TYPE ("USER_PROP") STORE AS SECUREFILE LOB (ENABLE STORAGE IN ROW CHUNK 8192 CACHE  NOCOMPRESS  KEEP_DUPLICATES  STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))

That’s It

You can use Data Pump to move queues around. For table-mode exports, remember to export the payload type separately.

A few interesting links:

Happy queueing!

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
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:

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