Drop Oracle Rule Set

I was trying to drop a user but couldn’t as it had QUEUE tables, queue rule andrule-set. So it required to drop ruleset before dropping the user. 

I tried the following and failed with a misleading error.

exec DBMS_RULE_ADM.DROP_RULE_SET(rule_set_name => ' AQ$WF_DEFERRED_QUEUE_M$1,delete_rules => false);

ORA-24170

string.string is created by AQ, cannot be dropped directly

Cause: This object is created by AQ, thus cannot be dropped directly

Action: use dbms_aqadm.drop_subscriber to drop the object

I kept searching how to drop subscriber, but nothing worked.

Here is the solution that worked:

select * from user_objects;

OBJECT_TYPE      OBJECT_NAME                              STATUS
RULE                  AQ$WF_DEFERRED_QUEUE_M$1      VALID
RULE SET            AQ$WF_DEFERRED_QUEUE_M$1      INVALID

1.Set the following event at session level:

grant alter session to ;

alter session set events '25475 trace name context forever, level 2';

2. Drop rule:

execute DBMS_RULE_ADM.DROP_RULE('.AQ$WF_DEFERRED_QUEUE_M$1',TRUE);
commit;

3.Drop rule set :

execute DBMS_RULE_ADM.DROP_RULE_SET('AQ$WF_DEFERRED_QUEUE_M$1');
commit;

4. Connect as SYSTEM or SYSDBA and try to drop user again.

drop user cascade;