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

Today I faced below mentioned Ora error when I tried to drop a user :-

SQL> drop user AMIT cascade;
drop user AMIT 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

There are queue tables present in the schema which needs to be dropped after that only we can drop the user.

Find out the queue tables in a schema :-

SQL> col OBJECT_NAME for a40

SQL> set lin 400

SQL> select object_name,object_type from dba_objects where owner='AMIT' and object_name like '%AQ%';

OBJECT_NAME                              OBJECT_TYPE

---------------------------------------- -------------------

DEF$_AQCALL                              TABLE

DEF$_AQERROR                             TABLE

SQL> conn AMIT/amit

Connected.

SQL> exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => 'DEF$_AQCALL' , force=>TRUE);

PL/SQL procedure successfully completed.

SQL> exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => 'DEF$_AQERROR' , force=>TRUE);

PL/SQL procedure successfully completed.

SQL> select object_name,object_type from dba_objects where owner='THINCLIENT' and object_name like '%AQ%';

no rows selected

SQL> conn /as sysdba

Connected.

SQL> drop user AMITcascade;

User dropped.

I hope this article helped you.

Regards,
Amit Rath