Re: v$lock

mballen_at_erols.com (Michael B. Allen) wrote in message news:<slrn9jlns6.1iu.mballen_at_nano.foo.net>...
> 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

The v$ tables are described in the Oracle 8i Reference Manual along with the init.ora parameters and all the dictionary views. You should be able to find the manual on your documentation CD.

• Mark D Powell --