Re: table locked from n minutes

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_at_cox.net 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_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------