Here is an interesting discussion on stopping long running queries by Tanel Poder:
in summary, it’s relatively easy to stop a long running query on UNIX but not on windows. I’ve known this for years, but not know exactly why except that windows somehow sets up the connection differently than UNIX. Why this hasn’t been address in all these years I don’t know. If you’ve ever tried a “cntrl-c” on windows sqlplus you know it doesn’t do much except possibly kill your window but the query keeps running on the database. In the case where your client PC dies or the connection breaks, the query happily continues to run on the database. Sort of annoying. In this case I have to in general kill the Oracle session on the database with the kill session command or use “kill -9” on the UNIX shadow process for the session. If I want the session to keep running and just cancel the query I can use “kill -URG” on the shadow process on UNIX, but if my database is running on windows how could I do that?
Tanel’s article points out that their might be solution using
You can set the consumer group for a session to CANCEL_SQL to cancel its current call:DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS ( session_id IN NUMBER, session_serial IN NUMBER, consumer_group IN VARCHAR2);