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

"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 !

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.

greetings, Jan Received on Tue Jan 13 2004 - 05:22:48 CST