Re: How to stop a running PL/SQL-procedure?

Ingo Ruth wrote in message <3492C454.36E_at_stud.uni-erlangen.de>...
>In one of these procedures I found a bug, but it has already startet -
>so how can I stop only this procedure without stopping the others?
>When I turned to Solaris to get a process-listing with ps -ef I couldn't
>find any of these processes.

You need to look at v$session to find the session which is running the buggy stored proc. This may be difficult if you can not identify the session id in v$session from columns such as MODULE or PROGRAM in v$session.

In that case, you can maybe use the following SQLs to find the session id of the misbehaved job (they display the sql statement(s) of active sessions): SELECT sid, sql_text FROM v_$open_cursor SELECT sid, sql_text FROM FROM v$sqlarea s, v$session p WHERE s.address = p.sql_address

>So is there any way to stop only one running procedure?

After you find the session id (sid) of the session, get the SERIAL# number too from v$session and use the command ALTER SYSTEM KILL SESSION 'sid,serial#';

IMHO it always a good idea to always make use of DBMS_APPLICATION_INFO.SET_CLIENT_INFO() call at the beginning of every stored proc you develop. This way you can easily see from the CLIENT_INFO column in v$session which stored procs are running - and it makes the life of a DBA so much easier when a user logs a performance problem. How often do you get a call from an irate Oracle user simply saying "my Oracle application is slow"? <and in the meantime he's whacking the network downloading jpegs from alt.binaries.erotica.. ;-)>

regards,
Billy Received on Wed Dec 17 1997 - 00:00:00 CST