DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Locking

  1. #1
    Join Date
    Jan 2001
    Posts
    138
    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.

  2. #2
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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:

    Code:
    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.




  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

  4. #4
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width