About Me

My photo
Bangalore, India
I am an Oracle Certified Professional working in SAP Labs. Everything written on my blog has been tested on my local environment, Please test before implementing or running in production. You can contact me at amit.rath0708@gmail.com.

Tuesday, March 19, 2013

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

6 comments:

  1. select object_name,object_type from dba_objects where owner='TT' and object_name like '%AQ%';
    when i am firing this command i am not getting any result
    how to find out queue table please let me know

    ReplyDelete
  2. select object_name,object_type from dba_objects where owner='TT' and object_name like '%AQ%';

    when i fires this command i am not getting any result.
    how to find out queue table for that particular user.

    ReplyDelete
    Replies
    1. Hi ,

      If you are getting "no rows selected" , means queue objects are not in your schema.

      Thanks
      Amit Rath

      Delete
  3. Very helpful. Thanks a lot

    ReplyDelete