Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: How to find USER who locked my row ...
Rafiq,
You are right. After running the catblock.sql script, I could see the views, but the output is "no rows selected". This is because the session that is accessing the locked row comes out with ORA-54 error instead of waiting for the lock to be released. There is a "nowait" clause at the end of the stmt. They don't want to session to wait if the row is locked by some other session, they just want to return ORA-54 error, but at the same time they want to know what user is locked the row at the time our session is accessing the row. If you have any other ideas, please let me know.
Thanks ... Babu
-----Original Message-----
Sent: Friday, February 02, 2001 11:58 AM
To: jbdonga_at_ucdavis.edu; ORACLE-L_at_fatcity.com
Babu,
Your system may be missing dba_blockers,dba_waiters views. Please look for script catblock.sql under $ORACLE_HOME/rdbms/admin and run it as per guidance given in that script.
Regards
Rafiq
To: "'ORACLE-L_at_fatcity.com'" <ORACLE-L_at_fatcity.com> CC: 'Mohammad Rafiq' <rafiq9857_at_hotmail.com> Date: Fri, 2 Feb 2001 10:59:20 -0800
Rafiq,
I tried to execute the query, It comes out with an error: dba_blockers does not exist. I verified in all of my Oracle 8i databases and have not found that view anywhere. Has it been removed in 8i? If not, I don't have any clue why it is not showing up?
Thanks...Babu
-----Original Message-----
Sent: Friday, February 02, 2001 9:56 AM
To: Multiple recipients of list ORACLE-L
Babu,
The message you have given pointing that object itself is being used and
some application trying to create index on that table or table definition
itself being changed.
Try following query which might help:
spool holding_session.lst
column object_name justify c heading "Object|Name" format a32 column username justify c heading "User|Name" format a7 column osuser justify c heading "OS|User" format a7 column pid justify c heading "Ora|Proc|ID" format 999 column serial# justify c heading "Ora|Serial|#" format 999999 column sid justify c heading "Holding|Session" format 999 column spid justify c heading "Unix|Proc" format a5 column object_id justify c heading "Obj|ID" format 99999 column lockwait justify c heading "Lock|Wait" column type justify c heading "Lock|Type" format a4 column lmode justify c heading "Mode" format 9999set pagesize 60 linesize 100
obj.object_id, ses.username, ses.osuser, lck.type, lck.lmode from dba_blockers blk, dba_objects obj, v$lock lck, v$session ses, v$process pro where blk.holding_session = ses.sid and lck.id1 = obj.object_id and lck.sid = ses.sid
Reply-To: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
Date: Fri, 02 Feb 2001 09:16:19 -0800
Steve,
Unfortunately, I can't take out NOWAIT as it is part of the application design. They don't want the query to hang for some other user to unlock the record. There are plenty of users accessing the same table and the same row at the same time. We want the query to return with ORA-54, but at the same time we would like to know the the user/user details who locked the record of the table. This is part of the requirement of this application here.
Is there a way to find the details that we need? Please let me know if there is any solution.
Thanks ... Babu
-----Original Message-----
Sent: Thursday, February 01, 2001 6:51 PM
To: Multiple recipients of list ORACLE-L
Hi Babu,
Take out the NOWAIT temporarily so that the session will wait, and then look
in
V$LOCK to see which TX lock you are waiting for and who is holding it.
@ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/
-----Original Message-----
Sent: Friday, 2 February 2001 9:50
To: Multiple recipients of list ORACLE-L
Dear list,
I am simulating an error generated by an application.
select * from XXX where fdoc_nbr = '12345' for update nowait;
It returns with error:
ORA-00054 Resource busy acquire with nowait specified.
I need to findout who(SID,SERIAL#,USERNAME) locked the same ROW (not table). There are many users who are locking different rows of the same table at the same time. I need to find the one who locked my row.
I would appreciate if someone could help me with this.
TIA,
-- Babu
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Steve Adams
INET: steve.adams_at_ixora.com.au
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Liststo: 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).
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
INET: jbdonga_at_ucdavis.edu
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Liststo: 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).
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mohammad Rafiq
INET: rafiq9857_at_hotmail.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Liststo: 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).
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Liststo: 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). Received on Fri Feb 02 2001 - 14:13:00 CST
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message