Skip to Main Content
  • Questions
  • Move tablespace for a queue table in 11g

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Kumar .

Asked: June 07, 2025 - 2:49 am UTC

Last updated: June 12, 2025 - 11:12 am UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

How to move a queue table into other tablespace in 11g database?

Can we shrink queue table in 11g database?

The dbms_redefinition is throwing error in moving to other tablespace as can't redefine.

My tablespace datafiles are very much fragmented because of this queue table as the queue table is spread across many datafiles in chunk and so it is necessary to move tablespace for the queue table in my 11.2.0.4 database.

The respective queue table is having 540+ million of records so even export expdp is being failed.

So asking can I either shrink the respective queue table or move to another tablespace?

and Chris said...

You can move queue tables online using DBMS_redefinition. MOS note 1410195.1 has a detailed description of the process and the code for a move_qt_pkg package for different versions to simplify this.

https://support.oracle.com/knowledge/Oracle%20Cloud/1410195_1.html

If all you want to do is reset the HWM and reclaim space in the table, you can do this as described in doc 421474.1:

In 10.2 onwards in when the queue table objects are stored in ASSM tablespaces you can do the following for the single and multi-consumer queue tables

alter table <queue_table_name> enable row movement;
alter table <queue_table_name> shrink space cascade;
alter table <queue_table_name> disable row movement;


https://support.oracle.com/knowledge/Oracle%20Database%20Products/421474_1.html

When you get to newer versions, there is a procedure in DBMS_AQADM to do this:

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 23ai database

https://support.oracle.com/knowledge/Oracle%20Database%20Products/304522_1.html

More to Explore

Data Pump

All of the database utilities including Data Pump are explained in the Utilities guide.