Or something…..

Blocking locks are easy enough to spot, through utllockt or v$session_waits or the goo-ey of your choice. The situation we had this morning seems to be that locks were being held which prevented a batch load from running. However, if you looked for them in a blocking locks type query you wouldn’t see diddley, ‘cos the batch load was bombing out straightaway. At least not unless you caught it at the right time

So, need to look at V$lock - in particular the ctime value. Found these:

0E3E08F0 0E3E09BC 20 TX 196617 877445 6 0 175785 0 1D95BCA4 1D95BCB8 20 TM 81940 0 2 0 175785 0 ADDR KADDR SID TY ID1 ID2 LMODE REQUEST ——– ——– ———- – ———- ———- ———- ———- CTIME BLOCK

1D95B9D4 1D95B9E8 20 TM 81942 0 2 0 175785 0 1D95BC2C 1D95BC40 20 TM 81933 0 3 0 175785 0 1D95B4AC 1D95B4C0 20 TM 81938 0 2 0 175785 0 ADDR KADDR SID TY ID1 ID2 LMODE REQUEST ——– ——– ———- – ———- ———- ———- ———- CTIME BLOCK

1D95B254 1D95B268 20 TM 81960 0 3 0 175785 0 1D95B614 1D95B628 20 TM 81952 0 3 0 175785 0 1D95B7F4 1D95B808 20 TM 81935 0 3 0 175785 0 Bounced the associated app, and all now ok

Note, 15 years on: I have have a clear idea of I was on about here, and I don’t do Oracle any more. I think I linked he ctime value to a ‘clock time’ and then tracked addr back to an application. I’m leaving it as published anyway a) just in case anybody else can decipher what I’m on about, and b) in case it becomes clear when I look at it next