Home » Server Options » Streams & AQ » Drop rule set (oracle 11g, OS-Linux)
Drop rule set [message #467228] Thu, 22 July 2010 14:57 Go to next message
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.
Re: Drop rule set [message #467360 is a reply to message #467228] Fri, 23 July 2010 09:54 Go to previous messageGo to next message
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
Re: Drop rule set [message #467361 is a reply to message #467360] Fri, 23 July 2010 09:58 Go to previous messageGo to next message
Michel Cadot
Messages: 58638
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the feedback.
Where did you find this solution?

Regards
Michel
Re: Drop rule set [message #467368 is a reply to message #467361] Fri, 23 July 2010 10:31 Go to previous message
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:
  


Current Time: Thu Jul 31 23:55:36 CDT 2014

Total time taken to generate the page: 0.27847 seconds