ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables

ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables

It appears that queue tables were not dropped cleanly in the past and may have some left over entries in the data dictionary.

Run following to get queue table name

select object_name, object_type, status from dba_objects where owner =’USERNAME’ and object_name like 'AQ%' order by object_name;

queue table name is on the middle AQ__X


For example: AQ$_RATINGCACHEQUEUE_I, queue table is RATINGCACHEQUEUE

You can first run following for each queue table

conn / as sys
exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => '.', force => TRUE);

For example:

For AQ$_TICKETQUEUE_H table, queue table is TICKETQUEUE, then

exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => 'USERNAME.TICKETQUEUE', force => TRUE);

and then carry rest of the manual cleaning steps from document Note 203225.1