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

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 dropt 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);

 

Advertisements

2 thoughts on “ORA-24005 must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s