Friday, September 17, 2010

Dropping user with QUEUE tables

I don't know whether it's a bug or something...but if you try to drop a user with cascade that is having some queue tables. It won't allow you to drop!!!
Would throw the below error:

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

How to ressolve?

1. Grant AQADM execute permission to the user

2. Login as scott and find the QUEUE tables it belongs to.

select * from user_tables
where table_name like 'AQ%'

3. Drop the QUEUE tables using the following command.
 exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'AQ$_IAI_NR',force => TRUE);

4. Login as DBA user and issue
drop user SCOTT cascade

Now it works!!!

No comments: