How to Manually Cleanup Oracle Advanced Queuing Tables

When the queue table is locked and not able to proceed, then the simplest way to rectify is to drop the queue table forcefully and create again using below sql.

BEGIN

 DBMS_AQADM.DROP_QUEUE_TABLE (

   queue_table         => 'queue_table',

   force               => TRUE,   auto_commit         => TRUE

 );

End;

/

Typical scenarios when a queue table/queue cannot be dropped

i) Cannot create or drop queue table DBMS_AQADM.CREATE_QUEUE_TABLE results in an

ORA-24001 cannot create QUEUE_TABLE, string already exists

Cause: The queue table already exists in the queueing system.

Action: Drop the table first using the DROP_QUEUE_TABLE() command or specify another table.

while DBMS_AQADM.DROP_QUEUE_TABLE results in an

ORA-24002 QUEUE_TABLE string does not exist

Cause: QUEUE_TABLE does not exist.

Action: Query on the user view USER_QUEUE_TABLES to find out existing queue tables.

ii) Cannot create or drop queue DBMS_AQADM.CREATE_QUEUE results in an

ORA-24006 cannot create QUEUE, string already exists

Cause: The queue requested to be created already exists.

Action: Specify another queue name. Query USER_QUEUES for all the

existing queues in the users's schema.

while executing DBMS_AQADM.DROP_QUEUE results in an

ORA-24010 QUEUE string does not exist

Cause: The specified queue does not exist.

Action: Specify a valid queue. Query USER_QUEUES for all the valid queues.