Re: Is it possible to check for objects that have been lock for 2 minutes or more in Oracle9i?

Jeffrey,

The following query maybe able to get you started. I haven't tested it against Oracle 9i but it definitely works under Oracle 8i.

select substr(to_char(w.session_id),1,5) "WSid",

                P1.spid "WPid",
                substr(s1.username,1,12) "WAITING User",
                substr(s1.osuser,1,8) "OS User",
                substr(s1.program,1,20) "WAITING Program",
                s1.client_info "WAITING Client",
                substr(to_char(h.session_id),1,5) "HSid",
                P2.spid "HPid",
                substr(s2.username,1,12) "HOLDING User",
                substr(s2.osuser,1,8) "OS User",
                substr(s2.program,1,20) "HOLDING Program",
                s2.client_info "HOLDING Client",
                o.object_name "HOLDING Object"
        from    v$process P1, v$process P2,
                v$session S1, v$session S2,
                dba_locks w, dba_locks h, dba_objects o
        where w.last_convert   > 120
        and  h.mode_held      !=  'None'
        and  h.mode_held      !=  'Null'
        and  w.mode_requested !=  'None'
        and  s1.row_wait_obj#  =  o.object_id
        and  w.lock_type(+)    =  h.lock_type
        and  w.lock_id1(+)     =  h.lock_id1
        and  w.lock_id2 (+)    =  h.lock_id2
        and  w.session_id      = S1.sid  (+)
        and  h.session_id      = S2.sid  (+)
        and  S1.paddr          = P1.addr (+)
        and  S2.paddr          = P2.addr (+)
        order by w.last_convert desc

HTH Roger.

"Jeffrey Yee" <jeffyee_at_hotmail.com> wrote in message news:ec4cec95.0305212150.52e1c54f_at_posting.google.com...
> Hi,
>
> I wonder if there is a way to check for objects that have been lock
> for 2 minutes or more, in Oracle9i database. Please advice. Thank you.
>
> Regards,
> Jeffrey
Received on Thu May 22 2003 - 05:51:33 CDT