My Oracle Support Banner

How to Move Queue Tables without using the Export or Datapump Utilities (Doc ID 304522.1)

Last updated on FEBRUARY 02, 2024

Applies to:

Oracle Database - Enterprise Edition - Version to [Release 8.1.7 to 10.2]
Oracle Database Cloud Schema Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.


The purpose of this article is to provide procedures, which enable you to move Advanced Queuing (AQ) tables without using the Export or Datapump utilities.


This article is intended for anyone who needs to move AQ tables from one tablespace to another or needs to reset the high water mark (HWM) of the queue table and associated objects which exist in 10.2 or earlier databases. 

It should be noted that although a 10.2 move package is supplied in this note in 10.2 onwards DBMS_REDEFINITION can be used instead to move queue tables. DBMS_REDEFINITION overcomes the limitation of the move procedure supplied in this note in that the move can be performed online.  Therefore, with DBMS_REDEFINITION  no down time is required as is the case if you use the move procedures supplied in this note which are using the alter table ... move command in a supported way. For this reason no further move procedures using this alter table move technique will be produced for 11.1 onwards. A procedure which uses DBMS_REDFINITION to move queue tables from 10.2 onwards can be found in <Note 1410195.1> and the concept is discussed in the documentation at the following URL

In addition to the ability to use DBMS_REDEFINITION to move a queue table and its associated objects in 10.2 onwards you also have the ability to dynamically shrink queue tables if they are located in ASSM tablespaces as per <Note 421474.1>.  Again this avoids the down time associated with this procedure.

In 9.2 the move procedure will also handle Streams Queues Tables. All Capture and Apply processes which access Queues in the Streams Queue Table must be disabled while you perform this maintenance operation and re-enabled afterwards.  Due to limitations with the underlying move operation the move procedure does not handle the lob segments associated with the AQ$_QUEUE_TABLE_D iot in 10.1 onwards. This means that in 10.1 onwards the move procedure will not move Streams Queue Tables completely. In 10.2 it also means it will not handle Queue Tables which use Buffered Messages. If you attempt to use the alter table move command on an object associated with a queue table outwith this procedure you will receive an ORA-08108 error. 

Newly database versions from 19c and onward can take advantage of the newly added procedure DBMS_AQADM.MOVE_QUEUE_TABLE to achieve such requirement in an easy and straightforward way. Note that two additional database patches are required for it to work within 19c and 21c databases: Patch 36051247 & Patch 34774667. Patches are not required for 23c database. Please reach out to Oracle Support Services via a Service Request for any questions.


To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.