Re: Killing an user from the user

From: Denix <denisenkoa_at_bigfoot.com>
Date: 5 Mar 2004 00:18:07 -0800
Message-ID: <2ece27de.0403050018.41d07e74_at_posting.google.com>

"Yong Feei Shun" <yong_feei_shun_at_uac.com.my> wrote in message news:<4047e081$1_2_at_news.tm.net.my>...
> Dear all,
>
> I am having a question here. hope you guys could help. Whenever
> i kill a user, i cant kill the oracle statement there, eventhough the user
> had been killed. What should be the problem? Does this mean that at the
> background that session still running? I encountered system slow down
> problem recently and cause me to kill the user whoever using the sesssion.
> Please help. Thanks
>
>
>
> Rgrds
> Feei Shun

Hi.

  If user session is executing PL/SQL block you can't kill that session
  instead this session well be marked for kill, and will be killed after PL/SQL statement executed
  so, if you really want to kill session with executing PL/SQL   you should kill Oracle server process for this session   (this recomendation not for Multy-Threaded Server model)   you can find server process PID by next query(written with RAC in mind):
  kill OS process with PID from query results - and you'll kill user session

--show_session_unix_pid.sql

SELECT i.instance_name AS inst_name,
       s.username,
       p.spid   pid,
       s.sid    sid,
       s.serial#,
       s.status,
       TO_CHAR(s.logon_time,'DD.MM.YYYY HH24:MI') logon_time,
       s.machine,
       s.osuser,
       s.program
  FROM gv$session s,
       gv$process p,
       gv$instance i
 WHERE s.paddr = p.addr

   AND p.inst_id=i.inst_id
   AND s.inst_id=i.inst_id
   AND type='USER'
   AND s.username LIKE '%&CONNECTED_USER%'  ORDER BY spid
/

Have a nice day.

Denix Received on Fri Mar 05 2004 - 09:18:07 CET