Queue Table Export-Import

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

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

A queue table that supports multiple recipients is associated with the following tables:

• A dequeue index-organized table (IOT)

• A time-management index-organized table

• A subscriber table (for 8.1-compatible queue tables)

• A history index-organized table (for 8.1-compatible queue tables)

These tables are exported automatically during full database mode and user mode exports, but not during table mode export. See "Export Modes".

Because the 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, since 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), you should correct the problem and repeat the import.

Export Modes

Exporting operates in full database mode, user mode, and table mode, as follows. Incremental exports on queue tables are not supported.

• Full database mode--Queue tables, all related tables, system-level grants, and primary and secondary object grants are exported automatically.

• User mode--Queue tables, all related tables, and primary object grants are exported automatically.

• Table mode--This mode is not recommended. If you need to export a queue table in table mode, you must export all related objects that belong to that queue table. For example, when exporting an 8.1-compatible multiconsumer queue table MCQ, you must also export the following tables:

  AQ$_<queue_table>_I (the dequeue IOT)	 
  AQ$_<queue_table>_T (the time-management IOT)
  AQ$_<queue_table>_S (the subscriber table)
  AQ$_<queue_table>_H (the history IOT)
  

Importing Queue Table Data

Similar to exporting queues, importing queues entails importing 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

A queue table that supports multiple recipients is associated with the following tables:

• A dequeue IOT

• A time-management IOT

• A subscriber table (for 8.1-compatible queue tables)

• A history IOT (for 8.1-compatible queue tables)

These tables must be imported as well as the queue table itself.

Import IGNORE Parameter

You should not import queue data into a queue table that already contains data. The IGNORE parameter of the import utility should always be set 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.