我们正在使用oracle> 10.0 这里和我们的软件创建不同类型的报告以评估存储的数据。由于数据量很大，创建此类报告可能需要几分钟的时间，并且客户希望停止查询。
有没有办法让 DMBS 停止查询？获得状态信息（例如已读取的行数）也很棒。
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.
发布评论
评论（3）
根据 Tanel Poder 的博客文章 此处，调用
DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS (sid,serial#, 'CANCEL_SQL');（其中sid和serial#标识要中断的会话）可能有效，但他没有测试过。如果您的应用程序可以进行
OCICancel()或 jdbcStatement.cancel调用，那么也可能有效。无论哪种情况，我都不确定如何获取已读取的行的状态；中断正在假脱机输出的 SQL*Plus 会话将告诉您它已输出多少行，但如果它尚未开始假脱机输出，则不会。
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 jdbcStatement.cancelcalls, 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.
如果您可以为查询定义截止时间（或其他资源）限制，请设置 Oracle 配置文件和资源限制，并让 RDBMS 负责终止会话：文档在这里
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
Adam 描述的一些关于 CANCEL_SQL 方法的有用帖子：
http://bitbach.wordpress.com/2009/11/02/implementing-cancel_sql-with-the-resource-manager/
http://www.oracle-base.com/articles/10g/ResourceManagerEnhancements10g.php
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