Re: v$lock

Mark D Powell wrote:
>"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<993633506.12696.0.nnrp-13.9e984b29_at_news.demon.co.uk>...
>> It's not normal -
>> have a look at v$session_wait for the calling
>> session when the query hangs.
>>
>> the 15 or so locks for SID = 2 are the Media Recovery
>> locks - one per file.
>>
>> I would consider enhancing your strategy by only
>> considering blocking transactions (V$LOCK.BLOCK=1),
>> and then only if their CTIME was more than a few seconds
>> and the thing they were block had also recorded a few
>> seconds.
>>
>> I would also be a little cautious about killing blocking
>> sessions if this would result in a large rollback - see
>> v$transaction.used_urec
>>
>>
>> --
>> Jonathan Lewis
>>
 

>> Michael B. Allen wrote in message ...
>> >If I do:
>> >
>> >select v$session.sid, v$session.username
>> > from v$session, v$lock
>> > where v$session.sid = v$lock.sid;
>> >
>> >the query returns about 15 entries with sid == 2 but then just hangs. Is
>> >this normal?
>> >
>> >I want to write two cgi scripts; one that produces a list of sessions
>> >holding locked records with username, machine, program, etc with a link
>> >to the second cgi script which will kill the session with alter system
>> >kill ...
>> >
>> >What's the best way to do this?
>> >
>> >Thanks,
>> >Mike
>
>Mike, I am asking this just to be sure as your post did not mention
>other code that determines that the sessions you intend to kill are in
>fact either blocking other sessions or are runaways. You do not need
>to reply if you, as is likely, have covered this.

Occasionally, one of several PowerBuilder clients abort abnormally leaving a record locked. Either the support desk needs to break the lock or the user must reboot to disconnect the socket. This is my theory anyway.

As a member of this "support desk" and an autodidact teaching myself Oracle installing it on the local Linux box and readins some old Oracle 7 manuals(I have Oracle8i: The Complete Reference as well) I am looking for excuses to examine Oracle innards. This seems like an excellent place to start yes? I have not yet explored John's suggestions but I understand clearly what he means. I'll post a follow up after I've had some time to look at it.

Is there a particular reference that describes the fields of tables in the data dictionary such as v$lock.id1 and v$lock.id2?

Thanks,
Mike Received on Thu Jun 28 2001 - 02:36:16 CDT