Re: Q:Cannot completely kill session in SQLDBA

John Jones <john_at_iluvatar.tip.duke.edu> wrote:

>Not sure if UNIX works the same as VAX, but we can use SQL*DBA to get the
>Oracle ID as well as the System ID. We then just go out to the system
>and kill the System ID. This takes away all Oracle processes that are
>using this System ID.
 

>--------------------------------------------------------------------
>John Jones | my views are my own.........
>Oracle Consultant | no matter what company they are
>john_at_iluvatar.tip.duke.edu | coming from.
>--------------------------------------------------------------------

This is possible using UNIX as well. In addition, add a line to the oracle.ini file on each client:

USERNAME='string'

with 'string' uniquely identifying that particular client station. Than, if you want to clean up a leftover session that originated from this client, run the following script:

select substr

         (decode
           (ses.type
           ,'USER', ses.username
           ,'BACKGROUND', 'Oracle BG'
           ,ses.type
           ), 1, 12
         )                          "Oracle User"
      ,ses.osuser                   "Client"
      ,prc.spid                     "Spid"
      ,ses.status                   "Status"
from   v$session   ses
      ,v$process   prc

which will produce output like this:

Oracle User Client Spid Status ------------ --------------- --------- --------

SYSTEM       oracle          29719     ACTIVE
USERNAME     'string'        29391     INACTIVE
Oracle BG                    375       ACTIVE
Oracle BG                    376       ACTIVE
Oracle BG                    377       ACTIVE
Oracle BG                    378       ACTIVE
Oracle BG                    379       ACTIVE

You can take the Spid to kill the session on the UNIX command line:

$kill -9 29391

BTW, I believe something can be done about this problem by doing some finetuning on TCP/IP. Has something to do with keepalive support, but at this moment that's all I can remember.

Hope this helps.

Bye,
Ruud

+----------------------------------------------------------------------+
   __              __
  /  ) __  __     /  )      __		Name	: Ruud de Gunst
 /    /_  /  /   /    /  / /  / /  /		  Oracle DBA
/    /__ /__/   /    /_ / /__/ /_ /	E-Mail	: ruud_at_wau.mis.ah.nl
                               __/		  redrudy_at_knoware.nl
+----------------------------------------------------------------------+