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);
You should read oracle docs. Killing the process or thread running a query is really the last option. In Windows you can kill a thread running a query using the mmc plug in installed with Oracle. Connect to the database instance, right click and one of the menu items gives you access to the Oracle threads. Here you can terminate them.
ReplyDeleteBut you should use ALTER SYSTEM KILL SESSION. An application can call OciBreak() to interrupt a statememt.
If your client terminates and the query is not killed, check your sqlnet parameters. Probably timeouts are set to 0 and the server doesn't check if the client is still alive until the result is ready.