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, [EMAIL PROTECTED] 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).