RE: to find who is waiting for lock

From: Amar Kumar Padhi <TS2017_at_emirates.com>
Date: Wed, 24 Oct 2001 09:24:55 -0700
Message-ID: <F001.003B3504.20011024093022@fatcity.com>

DBA_WAITERS
A table which gives information about sessions holding the lock and sessions waiting to lock the same object.

rgds
amar

-----Original Message-----
From: Tatireddy, Shrinivas (MED, Keane)
[mailto:Shrinivas.Tatireddy_at_med.ge.com]
Sent: Tuesday, October 16, 2001 4:50 PM
To: Multiple recipients of list ORACLE-L Subject: RE: to find who is waiting for lock

Thanq samir,

this will help me upto some extent, but not whole.

May I have a query to get, what transactions are being peformed (mainly inserts/updts , rather i can say inserted but not commited/updated but not commited) on a table?

SRinvias

-----Original Message-----
Sent: Tuesday, October 16, 2001 8:19 AM
To: 'ORACLE-L_at_fatcity.com'
Cc: Tatireddy, Shrinivas (MED, Keane)

Shrinivas,

Use the following query first to find the table name that corresponds to the

particular object_id :

select xidusn, object_id, session_id, locked_mode from v$locked_object;

After getting the object_id from the above query, u may get the object_name
holding the lock with the following query :
select object_name from dba_objects where object_id = 'xxxxx';

The enqueue mechanism of the Oracle server keeps track of the users waiting
for
locks held by other users, the lock modes these users acquire and the order
in which users requested the block.
If three users want to update the same row at the same time, all of them get
the
shared table lock but only the first one gets the row lock. The table locking
mechanism keeps track of who holds the lock and who waits for it. You can increase the number of locks available for an instance by increasing
the
parameters DML_LOCKS and ENQUEUE_RESOURCES.

Hope this helps.

Samir Sarkar
Oracle DBA - Lennon Team
SchlumbergerSema
Email : samir.sarkar_at_sema.co.uk
samir.sarkar_at_nottingham.sema.slb.com Phone : +44 (0) 115 - 95 76217
EPABX : +44 (0) 115 - 957 6418 Ext. 76217 Fax : +44 (0) 115 - 957 6018

-----Original Message-----
[mailto:Shrinivas.Tatireddy_at_med.ge.com]
Sent: 16 October 2001 12:10
To: Multiple recipients of list ORACLE-L

Hi dba's

is there any table/view/query to find out who is waiting to lock a table, that is already locked by somebody?

thnx in adv,
srinivas
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Tatireddy, Shrinivas (MED, Keane) INET: Shrinivas.Tatireddy_at_med.ge.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

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).

___
This email is confidential and intended solely for the use of the individual to whom it is addressed. Any views or opinions presented are solely those of the author and do not necessarily represent those of SchlumbergerSema.
If you are not the intended recipient, be advised that you have received this
email in error and that any use, dissemination, forwarding, printing, or

copying of this email is strictly prohibited.

If you have received this email in error please notify the SchlumbergerSema Helpdesk by telephone on +44 (0) 121 627 5600.

___
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Tatireddy, Shrinivas (MED, Keane) INET: Shrinivas.Tatireddy_at_med.ge.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------