joel-garry_at_home.com (Joel Garry) wrote in message news:<91884734.0401131707.45a6bd6f_at_posting.google.com>...
> "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
yeah, I'd wanna get their (user)name, rank and serial#.
(ok, that was an awful pun).
what I mean, is log what is being killed so the "root cause" (cough)
can be determined and the app code can be fixed so that they don't
need to be killed again next time.
again, IMHO, holding a lock that no other session is blocked by, is
not a capital offense. But if its blocking me, look out! (just
kidding).
Pd
Received on Wed Jan 14 2004 - 00:56:10 CST
