ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables
I was getting this error when I was trying to drop a schema .
Sql> drop user test 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
Logged in as Test user and checked for queue tables .
SQL> select * from user_queue_tables;
no rows selected
SQL> select OWNER, NAME, QUEUE_TABLE, ENQUEUE_ENABLED, DEQUEUE_ENABLED
from DBA_QUEUES where OWNER='TEST';
no rows selected
However I was able to locate few queue tables in the schema when I used
Sql> select table_name from user_tables;
Got few Tables starting with AQ$_ *******
Tried to delete these tables using the DBMS_AQADM.DROP_QUEUE_TABLE procedure . However ended with the following error message .
SQL> begin
2 DBMS_AQADM.DROP_QUEUE_TABLE(‘AQ$_test');
3 end;
4
5 /
begin
*
ERROR at line 1:
ORA-24002: QUEUE_TABLE SCOTT.AQ$_test does not exist
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 4084
ORA-06512: at "SYS.DBMS_AQADM", line 197
ORA-06512: at line 2