How to move queue (AQ) tables?

Normally to move a table from one tablespace to another, we will use the following command

ALTER TABLE <TableName> MOVE TABLESPACE <NewTablespaceName>;

But we cannot move Advanced Queuing table in this way.

To get information on advanced queue tables, we can query against dba_queue_tables .

We have to install a new package to move queue tables from one tablesapce to another. 

There are separate packages available in the Oracle Support Website for different Oracle versions.


For Oracle Database - Enterprise Edition - Version 8.1.7.0 to 10.2.0.5 [Release 8.1.7 to 10.2] 
Visit the below given MOS Note



From this note, we have to download a plb file for your database version and run it to install a new package named move_aqt

Then we can run the package as given below, with the owner name, queue table name and the name of the tablespace where the table has to be moved.

exec move_aqt.move('<OWNER>','<QUEUE_TABLE_NAME>', '<TABLESPACE_NAME>');

More information is available in the note given above.


For Oracle Database - Enterprise Edition - Version 10.2.0.5 to 11.2.0.3 [Release 10.2 to 11.2]
visit the following MOS note.

How to perform an Online Move of Advanced Queueing Tables using DBMS_REDEFINITION (Doc ID 1410195.1)

From this note, we have to download a plb file for your database version and run it to install a new package named move_qt_pkg

Then run the package with parameters as given below.

BEGIN
move_qt_pkg.move_queue_table('OWNER','QUEUE_TABLE_NAME','FROM_TABLESPACE','TO_TABLESPACE');
END;
/


More information is available in the note given above.
------------------------------------------------------------------------

If you are trying to move all objects from a tablespace where AQ tables exists, you may encounter the following error.

ALTER TABLE "APPS"."JTF_PF_LOGGING_TABLE" MOVE TABLESPACE APPS_TS_QUEUES STORAGE (INITIAL 524288 NEXT 524288) NOPARALLEL NOLOGGING 
fails with
ORA-00904: "SYS_NC00090$": invalid identifier

There is nothing to worry,  according to 


This table is not used in R12 and can be safely dropped running:

DBMS_AQADM.DROP_QUEUE_TABLE (queue_table => 'JTF_PF_LOGGING_TABLE', force => TRUE); 

No comments:

Post a Comment