Locking

[pcotten]

I have a script that gives me information on blocking locks. I need to create a cron job that will run every 30 minutes or so and emails/pages a DBA when a blocking lock exists for 30 minutes or longer. I thought it would be useful to create a temporary table, run the lock script and populate the table with any blocking locks. Then rerun the lock script in 30 minutes, if the same lock exists, then page/email the DBA. I am having trouble with making the syntax work. Anyone has similar script? Thanks.

[julian]

Have in mind that there are many harmless locks that might exists for more than 30', row exculsive on SYS._NEXT_OBJECT, etc.

Use this statement to find the locks:

select nvl(S.USERNAME,'Internal') username,
 nvl(S.TERMINAL,'None') terminal,
 L.SID||','||S.SERIAL# Kill,
 U1.NAME||'.'||substr(T1.NAME,1,20) tab,
 decode(L.LMODE,1,'No Lock',
  2,'Row Share',
  3,'Row Exclusive',
  4,'Share',
  5,'Share Row Exclusive',
  6,'Exclusive',null) lmode,
 decode(L.REQUEST,1,'No Lock',
  2,'Row Share',
  3,'Row Exclusive',
  4,'Share',
  5,'Share Row Exclusive',
  6,'Exclusive',null) request
from V$LOCK L,
 V$SESSION S,
 SYS.USER$ U1,
 SYS.OBJ$ T1
where L.SID = S.SID
and T1.OBJ# = decode(L.ID2,0,L.ID1,L.ID2)
and U1.USER# = T1.OWNER#
and S.TYPE != 'BACKGROUND'
order by 1,2,5;

Then populate the table with the data and check for same locks in the table. If such exist, email the info.

[pando]

if you execute cablock.sql in $ORACLE_HOME/rdbms/admin it will create several views for lockers, blockers etc Which are pretty useful when you have to diagnostic locking problems

[julian]

Originally posted by pando
if you execute cablock.sql in $ORACLE_HOME/rdbms/admin it will create several views for lockers, blockers etc Which are pretty useful when you have to diagnostic locking problems

Right. Another one is catparr. It is mostly used for OPS locking but can be used otherwise.