Re: Cancelling a Query/Procedure

> You need to kill the session of the tool that executed the query.
>
> 1. Connect as SYS (yes, you have to connect as sys)
> 2. Run the following query:
> set linesize 125
> set pagesize 100
>
> column username format a15;
> column osuser format a15;
> column kill format a40;
>
> select username,osuser,terminal,to_char(LOGON_TIME,'MMDD HH24:MI')
> logondate, 'alter system kill session ''' || sid || ','|| serial#
> ||''';' kill from sys.v$session order by 3;
>
> 3. Find the terminal and user that is running the offending query.
> 4. Copy the corresponing 'ALTER SYSTEM KILL SESSION ...' line and copy
> it.
> 5. Paste and execute it.
>
> Poof! Query stopped. Hope this helps.

Alter system kill session is OK *Only if DBA* needs to kill a runaway session. I hope you are not suggesting to grant "Alter System" privilege to end users where they would be able to kill a session by clicking on "Cancel" button (As inquired in the original post) ...this could result in a messy application and database.

The best thing to do is work on a good application design. If your application allows users to select 100,000 rows in middle of a workday...then that would be a very bad design as it could drastically result in your database performance if 100 users click ok at the same time.

A good application design would be to force users to narrow their search criteria. Generally, users seldom go beyond 100 rows in their resultsets before they themselves try to fine tune their query.

HTH
//Rauf Sarwar Received on Mon Aug 05 2002 - 05:31:41 CEST