Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Table Locks

RE: Table Locks

From: <Govind.Arumugam_at_alltel.com>
Date: Sat, 30 Nov 2002 05:03:37 -0800
Message-ID: <F001.0050F58F.20021130050337@fatcity.com>


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, 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: <Govind.Arumugam_at_alltel.com INET: Govind.Arumugam_at_alltel.com 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).
Received on Sat Nov 30 2002 - 07:03:37 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US