Resolving ORA-24005 when dropping a schema or a table

If a schema is holding the queue table, you will get the error ORA-24005 while dropping the schema or a table and you will need to use DBMS_AQADM to drop those tables. DBMS_AQADM also helps in dropping the queue table.   You can drop a queue table by just providing the table name to DROP_QUEUE_TABLE procedure of DBMS_AQADM package but you need to remember that all the queues in the queue table must be stopped and dropped before the queue table can be dropped.  In case if you did not stop and drop the queues in queue table you will get an error message ORA-24002: QUEUE_TABLE <table_name> does not exist which is misleading.  You must explicitly stop and drop all queue or an alternative method is that the force option is used, in which case the Oracle database will do that for you automatically.  Let’s follow this up with an example:
SQL> conn system/ decipher
Connected.

SQL> Drop user decipher cascade
/
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables

From the error message it is very clear that we need to use dbms_aqadm.drop_queue_table to drop table, but how can one find the queue tables?  The answer is to use the dba_queue_tables and below is the SQL to find the queue table.

SQL> Select QUEUE_TABLE from dba_queue_tables where owner = ‘DECIPHER’
QUEUE_TABLE
—————-
DECIPHER_AQ_DEMO

Or

SQL> conn decipher/decipher
Connected.

SQL> Select QUEUE_TABLE from user_queue_tables
QUEUE_TABLE
—————-
DECIPHER_AQ_DEMO

SQL> execute DBMS_AQADM.DROP_QUEUE_TABLE (queue_table => ‘DECIPHER_AQ_DEMO’);
BEGIN DBMS_AQADM.DROP_QUEUE_TABLE (queue_table => ‘ DECIPHER_AQ_DEMO ‘); END;

*
ERROR at line 1:
ORA-24002: QUEUE_TABLE DECIPHER_AQ_DEMO does not exist
ORA-06512: at “SYS.DBMS_AQADM_SYS”, line 4096
ORA-06512: at “SYS.DBMS_AQADM”, line 197
ORA-06512: at line 1

SQL> execute DBMS_AQADM.DROP_QUEUE_TABLE (queue_table => ‘DECIPHER_AQ_DEMO’, force => true);

Same can be used on getting ORA-24005 while dropping table

SQL> Drop table decipher_aq_demo
/
ORA-24005 MUST USE DBMS_AQADM.DROP_QUEUE_TABLE “