OraFAQ Forum: Streams & AQ

Home » Server Options » Streams & AQ » Drop rule set (oracle 11g, OS-Linux)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Drop rule set [message #467228]

Thu, 22 July 2010 14:57

nadvi
Messages: 46
Registered: May 2006
Location: Canada

Member

Hi,

I have only the following object (rule set) on my schema.

OBJECT_NAME OBJECT_TYPE
DEV_QUEUE_R RULE SET

I tried to drop with with following syntax:

exec DBMS_RULE_ADM.DROP_RULE_SET(
rule_set_name => 'DEV1.DEV_QUEUE_R',
delete_rules => false);

But following error shown:

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

And I couldn't find the exact syntaxt of this. Can anyone help me with the exact syntax of DBMS_AQADM.DROP_SUBSCRIBER?

Thanks.

Report message to a moderator

Re: Drop rule set [message #467360 is a reply to message #467228]

Fri, 23 July 2010 09:54

nadvi
Messages: 46
Registered: May 2006
Location: Canada

Member

Ok, I found the solution.

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:

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 <user> cascade;

Thanks

Report message to a moderator

Re: Drop rule set [message #467361 is a reply to message #467360]

Fri, 23 July 2010 09:58

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Thanks for the feedback.
Where did you find this solution?

Regards
Michel

Report message to a moderator

Re: Drop rule set [message #467368 is a reply to message #467361]

Fri, 23 July 2010 10:31

nadvi
Messages: 46
Registered: May 2006
Location: Canada

Member

Hi Michel,

I was googling and found this solution in a forum; which they mentioned as a excerpt from metalink. Though I couldn't find it there.

I tested it and worked for me Smile

Thanks.

[Updated on: Fri, 23 July 2010 10:31]

Report message to a moderator

Previous Topic:	10R2 Source/ 11gR2 Target applies to wrong destination
Next Topic:	Oracle streams with 10g source & 11g target

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 23 04:44:17 CST 2025