How to stop a query on in an oracle data base

Question

We are using oracle > 10.0 here and our software creates different types of reports for evaluation of the stored data. Because of the amount of data it is possible that such a report takes a couple of minutes to create and the customers would like to stop the queries.

Is there a way to say the DMBS that it should stop a query? It would also be great to get state information like a count of rows allready read.

Answer 1

According to a blog post from Tanel Poder here, a call to DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS (sid, serial#, 'CANCEL_SQL'); (where sid and serial# identify the session to be interrupted) may work, but he's not tested it.

If your application can make OCICancel() or jdbc Statement.cancel calls, that may work as well.

In either case, I'm not sure how to get a state of rows already read; interrupting a SQL*Plus session that's spooling output will tell you how many rows it has output, but if it hasn't started spooling output, it won't.

Answer 2

If you can define a cut-off time (or other resource) limit for the queries, set up Oracle profiles and resource limits and let the RDBMS take care of killing the sessions: doc here

Answer 3

Check out the top answer in this post too...

http://stackoverflow.com/questions/466963/is-it-possible-to-kill-a-single-query-in-oracle-without-killing-the-session

Answer 4

A couple of helpful posts on the CANCEL_SQL approach described by Adam:

http://bitbach.wordpress.com/2009/11/02/implementing-cancel_sql-with-the-resource-manager/

http://www.oracle-base.com/articles/10g/ResourceManagerEnhancements10g.php