DROP QUEUE TABLES

While trying to drop a user with QUEUE table objects; it won't let you drop with below warnings.

ORA-00604: error occurred at recursive SQL level 1
ORA-24170: SCOTT.TEST_QUEUE_R is created by AQ, cannot be dropped directly
ORA-06512: at "SYS.DBMS_RULE_ADM", line 158
ORA-06512: at line 1

It requires to drop QUEUE tables first from user and then can be dropped.

--FIND THE QUEUE TABLES BY LOGGING as USER SCOTT.

select * from dba_tables
where table_name like 'AQ%'
and tablespace_name = 'USERS'

-- Grant execute on DBMS_AQDM to the user SCOTT having queue tables.

GRANT EXECUTE ON DBMS_AQADM to SCOTT


-- LOGIN AS SCOTT DROP QUEUE TABLES

select * from user_tables
where table_name like 'AQ%'

exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'AQ$_TEST_IAI_NR',force => TRUE);

--- NOW DROP SCOTT as SYS