Re:kill session

Bill,

    I agree, granting alter system to users is a BAD thing to do. What would be better is for the developer to:

1. define what a reasonable time is for a statement to execute in. Then put that into the users profile so the statement terminates by itself.
2. give the end user a confirmation button before the database tries to run the query.

The problem with long running queries is that once the database starts processing it ignores the client until the first row of data is ready to be sent back. Possible he should use the 'first rows' hint to speed things up.

Dick Goulet

____________________Reply Separator____________________
Author: becker.bill_at_marshfieldclinic.org
Date:       10/17/2002 8:54 AM

Hello,

I received a request from a developer, who is working on a client C++ front end application intended to perform semi-customized queries against a star schema in a datawarehouse. By semicustomized,  I mean the fields selected are usually the same, the user can change date ranges, age ranges, etc., affecting the where clause. This app communicates with Oracle 8.1.7.4 server on Solaris via an oracle8 ODBC driver. The developer is looking for a way to allow the user to interrupt/kill any queries from the application. Here is an excerpt of his request:
>>

Apparently the Oracle8 ODBC drivers don't support asynchronous execution at all, so it can't be done through ODBC. After hunting through Oracle documentation, I've considered starting the query in one thread, and killing it in another with a command like ALTER SYSTEM KILL SESSION '[sid], [serial#]'.

A couple of questions:

1. How big a deal would it be to grant users the ALTER SYSTEM privilege, so <app_name> could kill their sessions? Would this have the desired effect of just abandoning the query?
2. Are you aware of any other, better ways to kill a query, given that ODBC doesn't directly support it?
   >>
   

I am hesitant to grant alter system to users. Does anyone have a better suggestion?
Thanks

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: becker.bill_at_marshfieldclinic.org

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: dgoulet_at_vicr.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).