Re: kill session that holds lock for more then 10 sec

"Jan Gelbrich" <j_gelbrich_at_westfalen-blatt.de> wrote in message news:<bu0kjo$cbk5k$1_at_ID-152732.news.uni-berlin.de>...
> "Harald Maier" <harald_at_maierh.de> schrieb im Newsbeitrag
> news:m38ykcm7m1.fsf_at_ate.maierh...
> > "Volker Hetzer" <volker.hetzer_at_ieee.org> writes:
> >
> > > "Paul Drake" <drak0nian_at_yahoo.com> schrieb im Newsbeitrag
> > > news:1ac7c7b3.0401122252.dcaf2d9_at_posting.google.com...
> > >> ALTER SYSTEM KILL SESSION is not the way to go.
> > >>
> > >> you could conceivably wait hours for the session to be killed and
> > >> the lock(s) to be released. don't go with a DBMS_JOB, schedule an
> > >> OS task so that you can leverage orakill.exe. that will seriously
> > >> kill a session.
> > >>
> > >> basically, use a batch file to call sqlplus. in sqlplus run a
> > >> script to spool an orakill script quit sqlplus and execute the
> > >> spooled .bat file for bonus points, you could log the offending
> > >> user that held the lock too long (although 60 seconds seems to be a
> > >> little too short to me).
> > >>
> > >> D:\oracle\ora81\bin>orakill
> > > Does something like this exist on unix too?
> >
> > orakill allows under Windows to kill a thread of the one Oracle
> > Process. Under Unix Oracle is designed to use many processes so there
> > is no necessity to have a orakill utility. The Unix kill command is
> > enough.
> >
> > Harald
>
> I would almost never kill the UNIX process but rather the session by KILL
> SESSION,
> because the lock and the session status 'KILLED' has a _meaning_:
> it cleans up after the user, and so long the lock is hold,
> but if You kill the UNIX process before time,
> You may have Your database corrupted just because of senseless impatience !

On the other hand, the unix kill gets it cleaned up by PMON, while the session kill waits on SMON. In the past, SMON was sometimes way too busy, while PMON was rapid. I have the general impression that SMON is once again getting lots of responsibility as the Oracle versions progress.

Can't say as I've seen a corruption from PMON, but I've sure seen SMON be worse than useless for this. And of course, seen people kill the wrong process both ways.

>
> I use a DBMS_JOB running every 3 minutes, and if a user is detected twice
> blocking others
> (locking itself is not critical), then he gets killed - if he/she is within
> a defined group of everyday users.
> Of course, I do not kill admin sessions. All actions are logged for further
> investigations.

Man, that sounds like nasty DBA. Shoot first and let The Committee handle it.

>
> greetings, Jan

jg

--
@home.com is bogus.
http://www.imdb.com/title/tt0072730/plotsummary