Re: table locked from n minutes
- From: Mladen Gogala <mladen@xxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 2 Apr 2004 10:43:53 -0500
Why do you want to do things with the sysdate? There is CTIME column in V$LOCK
which contains
the time in seconds (rounded, as usual, to the nearest 3 seconds) since the
lock was granted.
V$LOCK is the best table to use, not V$LOCKED_OBJECT. The column ID1 in V$LOCK
represents OBJECT_ID
from DBA_OBJECT, if ID2=0. Any session that wants to lock rows needs to lock
table in S/Row-X mode (5).
So, in order to murder long locking sessions, you need only mode 5. If you
allow 6 (exclusive mode),
then there is a problem with the application. Anybody locking tables in
exclusive mode on an OLTP
system should be publicly beheaded.
On 04/02/2004 10:09:56 AM, solbeach@xxxxxxx wrote:
> select object_name, do.object_id, session_id, serial#, osuser, username,
> locked_mode , start_time, module
> from dba_objects do, v$session, v$locked_object lo, v$transaction
> where to_date(start_time,'MM/DD/YY HH24:MI:SS') <
> (sysdate-(10/1440))
> and locked_mode in (3,5,6)
> and session_id = sid
> and saddr = ses_addr
> and lo.object_id = do.object_id
> order by start_time desc
> /
>
--
Mladen Gogala
Oracle DBA
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
