Migrating Queue Tables (Import-Export)

When a queue table is exported, the queue table data and anonymous blocks of PL/SQL code are written to the export dump file. When a queue table is imported, the import utility executes these PL/SQL anonymous blocks to write the metadata to the data dictionary.

Exporting Queue Table Data

Queues are implemented on tables. The export of queues entails the export of the underlying queue tables and related dictionary tables. Export of queues can only be done at queue table granularity.

Exporting queue tables with multiple recipients

For every queue table that supports multiple recipients, there is an index-organized table (IOT) and a time-management table that contain important queue metadata. For 8.1 compatible queue tables there is also a subscriber table, a history table and a rules table. This metadata is essential to the operation of the queue, so the user must export these tables as well as the queue table itself for the queues in this queue table to work after import. During full database mode and user mode export, all these tables are exported automatically.

Because these metadata tables contain rowids of some rows in the queue table, the import process will generate a note about the rowids being obsoleted when importing the metadata tables. This message can be ignored as the queuing system will automatically correct the obsolete rowids as a part of the import operation. However, if another problem is encountered while doing the import (such as running out of rollback segment space), the problem should be corrected and the import should be repeated.

Exporting Rules

Rules are associated with a queue table. When a queue table is exported, all associated rules, if any, will be exported automatically.

Supported Export Modes

Export currently operates in three modes: full database mode, user mode, and table mode. The operation of the three export modes is described as follows.

Full database mode

This mode is supported. Queue tables, all related tables, system level grants, and primary and secondary object grants are exported automatically.

User mode

This mode is supported. Queue tables, all related tables and primary object grants are exported automatically.

Table mode

This is not recommended. If there is a need to export a queue table in table mode, the user is responsible for exporting all related objects which belong to that queue table. For example, when exporting an 8.1 compatible multi-consumer queue table MCQ, you will also need to export the following tables:

AQ$_MCQ_I 
AQ$_MCQ_H 
AQ$_MCQ_S 
AQ$_MCQ_T  

Incremental export

Incremental export on queue tables is not supported.

Importing Queue Table Data

Similar to exporting queues, the import of queues entails the import of the underlying queue tables and related dictionary data. After the queue table data is imported, the import utility executes the PL/SQL anonymous blocks in the dump file to write the metadata to the data dictionary.

Importing queue tables with multiple recipients

As explained earlier, for every queue table that supports multiple recipients, there is a index-organized table (IOT), a subscriber table, a history table, and a time-management table that contain important queue metadata. All these tables as well as the queue table itself, have to be imported for the queues in this queue table to work after the import.

Because these metadata tables contain rowids of some rows in the queue table, the import process will issue a note about the rowids being obsoleted when importing the metadata table. This message can be ignored, as the queuing system will automatically correct the obsolete rowids as a part of the import operation. However, if another problem is encountered while doing the import (such as running out of rollback segment space), the problem should be corrected and the import should be rerun.

Import IGNORE parameter

We suggest that you do not import queue data into a queue table that already contains data. We recommend that the DBA should always set the IGNORE parameter of the import utility to NO when importing queue tables. If the IGNORE parameter is set to YES, and the queue table that already exists is compatible with the table definition in the dump file, then the rows will be loaded from the dump file into the existing table. At the same time, the old queue table definition and the old queue definition will be dropped and recreated. Hence, queue table and queue definitions prior to the import will be lost, and duplicate rows will appear in the queue table.