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

Error occurred during drop the user with cascade option releasing all the space and objects space backup to datafiles. When we got this error we need to manually drop the queue table force fully with DBMS_AQADM package.

On executing the drop command for user:

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

Solutions

1. Check the list of table present in the schema:

select object_name,object_type from dba_objects where owner='SCOTT' and object_name like '%AQ%';

2. List of table to be dropped with the following procedure:

DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => 'table_name', force =>TRUE);

3. After drop table try again with delete user command:

SQL> drop user cascade;


Note: If you want to drop from particular user then assign him permission:

GRANT execute ON dbms_aqadm TO USER;
conn /
execute DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => 'table_name', force =>TRUE);