[askdba] Re: Session Killed...!!
- From: Ganesh Raja <ganesh.raja@xxxxxxxxx>
- To: askdba@xxxxxxxxxxxxx
- Date: Tue, 20 Sep 2005 08:34:34 +0100
The Reason for it taking so Long is bcos the session has not come back to
Oracle Asking for more data so it still does not it has been Killed ...
A Zombie Session working on its own .. What you can do to avoid these
problems is to set the Idle time settings in sqlnet.ora so that the session
is killed automatically if it is not responding ...
HTH
- Ganesh
On 15/09/05, Luis Fernando Cerri <lcerri@xxxxxxxxxxxxxxxxxxxxxxx> wrote:
>
> For my development databases running on unix-like systems, I created a
> stored procedure allowing developers to kill their own sessions. The stored
> procedure has an exception to catch the error ORA-00031(session marked for
> kill), so when it happens, it calls a shared lib that kills the process id
> in the OS.
> Sorry for the messages and coments in portuguese, but I'm sure all you
> can understand the idea:
> ----> cut here and break your display<----
> create or replace procedure p_killme(vsid in number, vserial# in number)
> as
> vusername varchar2(100) := '';
> vpid integer := 0;
> session_marked_for_kill exception;
> pragma exception_init(session_marked_for_kill,-31);
> begin
> select s.username,p.spid
> into vusername,vpid
> from v$session s,v$process p
> where s.sid=vsid
> and s.serial#=vserial#
> and s.paddr = p.addr;
> if vusername = user then
> begin
> execute immediate 'alter system kill session
> '''||vsid||','||vserial#||''''; -- mata a sessao
> exception
> when session_marked_for_kill then
> sys.p_killpid(vpid); -- mata o processo referente a sessao
> end;
> else
> raise_application_error(-20902,'Impossivel matar sessoes de outro usuario
> que nao o '||user||'.');
> end if;
> exception
> when no_data_found then
> raise_application_error(-20903,'Nao existe uma sessao com essas
> caracteristicas.');
> end;
> --> end cut <---
> []s
> Luis
>
> -----Original Message-----
> *From:* Chen, Sarah [mailto:Sarah_Chen@xxxxxxxxx]
> *Sent:* quinta-feira, 15 de setembro de 2005 11:54
> *To:* 'Thomas.Mercadante@xxxxxxxxxxxxxxxxx'; 'chiragdba@xxxxxxxxx';
> 'Oracle-L Freelists'
> *Subject:* RE: Session Killed...!!
>
> I always check process id as well as sid and serial# while killing a
> session from Oracle.
> I will always query v$session before and after, and if the status of
> v$session marked "killed", and then I will go ahead kill OS process to clean
> this session. It always works on Solaris.
> Sarah
>
> -----Original Message-----
> *From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:
> oracle-l-bounce@xxxxxxxxxxxxx]*On Behalf Of *Mercadante, Thomas F (LABOR)
> *Sent:* Thursday, September 15, 2005 10:47 AM
> *To:* chiragdba@xxxxxxxxx; Oracle-L Freelists
> *Subject:* RE: Session Killed...!!
>
> If you kill the unix process associated with this session first, and then
> the session it will go away.
>
> Killing sessions in Oracle has always been weird. Sometimes they go away
> and sometimes not, depending on OS. In Windows, use orakill to kill the
> session - this always seems to work in windows. And killing the unix session
> then the Oracle session seems to work just fine in AIX.
>
> ------------------------------
>
> *From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:
> oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Chirag DBA
> *Sent:* Thursday, September 15, 2005 10:36 AM
> *To:* askdba@xxxxxxxxxxxxx; Oracle-L Freelists
> *Subject:* Session Killed...!!
>
> Hi ,
>
> I saw many users on my database connected for more than 6 days without
> any activity.
>
> I killed 1 user and still the status in v$session is showing as killed.
>
> but it it not getting removed from the v$session.
>
> I am running 9.0.1.3 <http://9.0.1.3> on solaris.
>
> any idea? We already hit the bug ora-07442.
>
> regards - chirag
>
>
Other related posts:
