Re: Abort a Query while processing??

lcrusader_at_my-deja.com wrote in message <87r2lf$s0t$1_at_nnrp1.deja.com>...
>We access data from Oracle 7 & Oracle 8 through Pro*C modules. One of
>our queries is taking quite a long time to process
>(more than 10 minutes). I would like to abort the query based on a
>time-out limit and take necessary action. This is possible for
>a Unix process, but can this be done to an Oracle query?

You will need to have some kind of monitoring process to do it. Either a UNIX chron job or an internal Oracle job that executes at regular (1 minute?) intervals.

From V$SESSION you can determine the list of jobs that can be killed, including the current "elapsed" time of a session.

If the job meets the criteria to be killed (which usually means any job from a user I don't like on that particular day), you use the ALTER SYSTEM KILL SESSION sql command to kill it.

Word of warning though - many jobs will be marked as kill in V$SESSION and the actual Unix process for that Oracle job may run several minutes and more as Oracle rolls the transactions back of that killed session. In some cases, (a bug?) the killed session will remain until the Oracle instance is bounced.

Billy Received on Wed Feb 09 2000 - 06:55:33 CST