How to stop query in oracle database

Question

We use oracle> 10.0 here, and our software generates various types of reports to evaluate stored data. Due to the amount of data, it is possible that such a report takes a few minutes , and customers would like to stop requests.

Is there a way to tell DMBS that it should stop the request? It would also be great to get status information, such as the number of lines read allready.

Answer 1

According to Tanel Poder's blog post here , calling DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS (sid, serial#, 'CANCEL_SQL'); (where sid and serial # identify the session is being interrupted) may work, but it did not test it.

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

In any case, I'm not sure how to get the status of the lines already read; terminating a SQL * Plus session in which buffering output will tell you how many rows it has, but if it did not start buffering output, it will not.

Answer 2

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

Answer 3

Some useful messages in 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