We run the following script every 20 minutes to identify the pending transactions ( to
be committed) and notify the appropriate application group (online or batch ) to take
action in consultation with the DBA group.
We filter this by username since we have some convention for batch programs and online
programs; We set thresholds for minutes_pending > 5 minutes for onlines and > 60 for
batch.
select sysdate, '1' inst_id, sid, serial#, username, substr(terminal,1,10) termi
nal, osuser,
t.start_time, r.name, t.used_ublk "ROLLB BLKS",
decode(t.space, 'YES', 'SPACE TX',
decode(t.recursive, 'YES', 'RECURSIVE TX',
decode(t.noundo, 'YES', 'NO UNDO TX', t.status)
)) status, round( ( sysdate - TO_DATE( start_time, 'MM/DD/YY HH24:MI:SS')
) *24*60 ,0 ) minutes_pending
from v$transaction t, v$rollname r, v$session s
where t.xidusn = r.usn
and t.ses_addr = s.saddr
order by t.start_time;
Hope this helps.
Govind
-----Original Message-----
Sent: Saturday, November 30, 2002 12:39 AM
To: Multiple recipients of list ORACLE-L
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: <[EMAIL PROTECTED]
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).
