Re: Table Locks

Seems to me you should just have your program try to lock tables in exclusive mode. If it succeeds, then rollback. If it fails (timeout), it opens another session while the 'lock table' is waiting, and finds the blocker.

Otherwise, if you are only interested in sessions that are actually blocking other sessions, just look in v$lock where block = 1.

As interesting as it seems, I think you won't succeed in trying to put triggers on x$kgllk or anything like that. They're not real tables - just table-like accessors for memory structures in the SGA.

--
Jeremiah Wilton
http://www.speakeasy.net/~jwilton

On Fri, 29 Nov 2002, prem_at_ibsplc.com wrote:



> I would like to send an alert message to a client when a data row is 

> locked for more than a certain period of time. For this can I write 

> triggers on the system tables. If so on which table should I write a 

> trigger to retrieve the table lock information. Are there any implications 

> on writing triggers on the system tables.

> 

> The alert message should be sent automatically in the sense, can I write 

> an alert and signal it from a trigger written on some system table where 

> the lock information is available?


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jeremiah Wilton
  INET: jwilton_at_speakeasy.net

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).