Re: Identifying a locked record

From: Yves Noel <noel_at_citi.citilille.fr>
Date: 13 Jul 1993 08:02:13 GMT
Message-ID: <21tq65$ifp_at_netserver.univ-lille1.fr>

In article <C9wH0z.1C7_at_cbfsb.cb.att.com>, colten_at_cbnewsb.cb.att.com (marc.colten) writes:
|>
|> I'm trying to find a way to identify who is using a particular
|> record. Users find themselves locked out when another user
|> has the record up in a form (SQL*FORMS30). I have checked
|> the database adminstrator's book and tried to cross reference
|> the information in V$PROCESS, V$_LOCK, V$SESSION and others,
|> but nothing brings me closer to linking a user to the record
|> they are looking at.
|>
|> thanks in advance for any advice.
|>
|> marc colten

In fact, this problem is not trivial ! BUT there's a begin of solution in an article writed in the 'Bulletin Technique ORACLE FRANCE' number 8 - summer 1992 - which is titled 'Identify the actors of a deadlock' (dossier 8.7). To detect these locks you must play with the trace file which is generated. Particularly, you must retrieve the users's process PID who have caused the lock, then you can retrieve the users names and finally a char string RW (Row Wait). This last information is used to retrieve the resource, numbers are hexadecimal

ones : _ the two first char of the first number are the file id
       _ the six following are the block id
       _ the second number is the internal row number in this block.

The repartition two and six char is Oracle block size dependant. Last you use this SQL script to retrieve the resource with the dba_extents table :

	select * from sys.dba_extents
	where NNN between block_id and (block_id + blocks)
	and file_id = NN;
where NN is the file id,
      NNN the block id in decimal.

--
==============================================================================
NOEL Yves                                        |  Phone : 33.20.43.42.70
Oracle DBA                                       |  Fax   : 33.20.43.66.25
C.I.T.I.                                         |  Email : noel_at_citilille.fr
Universite des Sciences & Technologies de Lille  |
59655 Villeneuve d'Ascq Cedex - FRANCE           |
==============================================================================
----             I use ORACLE v6.0.36 on DEC RISC ULTRIX v4.3             ----
==============================================================================

Received on Tue Jul 13 1993 - 10:02:13 CEST