Re: terminating a recursive PL/SQL loop

Hi

See if this will helps.

First, run this script and get sid and serial#:

set wrap on pause off
alter session set nls_date_format = 'dd-MON-yyyy hh24:mi:ss'; select username, osuser, terminal, program, machine, logon_time,

         sid, serial#
  from v$session;
exit

Then, use the script below to kill the session:

alter system
 kill session ' &SID, &SERIAL_NO'
/

Oracleguru
www.oracleguru.net
oracleguru_at_mailcity.com

Arno Tigges <arno.tigges_at_bertelsmann.de> wrote in article <35E3D907.2B817F4_at_bertelsmann.de>...
> What can be done to terminate an accidentally coded PL/SQL endless
> recursive loop. Just killing the session does not work. The session
> becomes the status "KILLED" but the procedure is still running and after
> a while the server is running out of memory! Last week we have to
> _REBOOT_ a production server to get rid of such an endless recursion,
> because of the memory overload we were not able to shut down the
> instance normally.
>
> Killing a session with a procedure in a "normal" endless loop works
> fine, but unfortunately not for a much more problematic endless
> recursion.
>
> Does anybody know a solution for that problem? Any advice is welcome!
> The Oracle Version is 7.3.2.3.5 and it runs on a NT 3.51 server.
>
> Arno Tigges
>
>
>
>
Received on Wed Aug 26 1998 - 16:20:33 CDT