Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: How to find USER who locked my row ...

RE: How to find USER who locked my row ...

From: Janardhana Babu <jbdonga_at_ucdavis.edu>
Date: Fri, 02 Feb 2001 14:40:41 -0800
Message-ID: <F001.002A9AC6.20010202142523@fatcity.com>

Ferris,

Thanks for your help. I also modified the script, but couldn't get the result that I wanted. Iam getting all userIds that are accessing at the table level. I would like to explain the question in another way. I issued the following stmt:

select * from xxxx where doc_nbr = '000012345' for update nowait;

It comes out with error ORA-54 saying that some other userid is updating the same row. I would like to know the the userid who locked my row.

I need to get some info like 'address of the row' from my above stmt. and get the userid who locked the same row. If I can get the row_wait_row# value of the above stmt, then, I can use it in the join stmt on row_wait_row# column of the v$session to get the SID,SERIAL#,USERNAME etc.

I don't know how to get the row_wait_row# value for my above stmt. In V$session it is displaying numbers such as 8, 12, 16etc. If this gives you any clue, please try to help me.

Thanks...Babu

-----Original Message-----
Sent: Friday, February 02, 2001 12:55 PM To: 'Janardhana Babu'
Importance: Low

Try this.. (tailor as necessary)

column sid     format 99999 heading 'SID'
column osuser  format a9    heading 'OS-User'
column type    format a14   heading 'Type'
column lmode   format a14   heading 'Line Mode'
column request format a15   heading 'Request'
set termout on
rem spool lock.lis

select A.sid, osuser, A.type,

       decode(lmode, 2,'Row Share', 3,'Row Excl', 4,'Share',
                     5,'Row Share Excl',6,'Table Excl', lmode) lmode,
       decode(request, 2,'Row Share', 3,'Row Excl', 4,'Share',
                       5,'Row Share Excl', 6,'Table Excl', request) request
from sys.v_$lock A, sys.v_$session B
where A.sid = B.sid
and osuser is not null
order by A.sid
/

HTH
Shawn M Ferris
Oracle DBA - Time Warner Telecom

> -----Original Message-----
> From: Janardhana Babu [mailto:jbdonga_at_ucdavis.edu]
> Sent: Friday, February 02, 2001 1:33 PM
> To: 'Ferris, Shawn'
> Subject: RE: How to find USER who locked my row ...
>
>
> Ferris,
>
> Thanks for your help. I ran catblock.sql and also
> utllockt.sql. I could see
> the views now. But the output of utllockt.sql is "no rows
> selected" as no
> session is waiting. There is a session that locked the row. If another
> session tries to access the same row of the same table, it
> comes out with
> error ORA-54 as this session is accessing with NOWAIT
> specified. NOWAIT is a
> requirement in the application. In other words, I just want
> to know who
> locked the row. My session just doesn't like to wait if the
> row is being
> accessed by someone. I just would like to return my stmt with
> ORA-54, but I
> would like to know who locked the row because of which Iam
> kicked out with
> ORA-54. Please let me know if there are any ideas.
>
> Thanks ... Babu
>
> -----Original Message-----
> From: Ferris, Shawn [mailto:Shawn.Ferris_at_twtelecom.com]
> Sent: Friday, February 02, 2001 11:46 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: How to find USER who locked my row ...
>
>
> You'll need to run catblock.sql, located in
> $ORACLE_HOME/rdbms/admin. This
> will give you the views that you'll want. utllockt.sql will
> give a "tree
> view" of waiters and their blockers too. (catblock.sql required for
> utllockt.sql)
>
> HTH
> Shawn M Ferris
> Oracle DBA - Time Warner Telecom
>
> > -----Original Message-----
> > From: Janardhana Babu [mailto:jbdonga_at_ucdavis.edu]
> > Sent: Friday, February 02, 2001 12:00 PM
> > To: Multiple recipients of list ORACLE-L
> > Subject: RE: How to find USER who locked my row ...
> >
> >
> > 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 9999
> > set pagesize 60 linesize 100
> > select lck.sid, ses.serial#, pro.pid, pro.spid, obj.object_name,
> > 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
> > and ses.paddr = pro.addr
> > and ses.username is not NULL
> > order by obj.object_name, ses.lockwait desc, lck.sid;
> > spool off
> > clear columns
> > /
> > Regards
> > Rafiq
> >
> >
> >
> >
> >
> >
> >
> > 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 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).
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Janardhana Babu
> > INET: jbdonga_at_ucdavis.edu
> >
> > 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).
> >
> > _________________________________________________________________
> > Get your FREE download of MSN Explorer at http://explorer.msn.com
> >
> > --
> > 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 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).
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Janardhana Babu
> > INET: jbdonga_at_ucdavis.edu
> >
> > 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).
> >
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Ferris, Shawn
> INET: Shawn.Ferris_at_twtelecom.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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Janardhana Babu
  INET: jbdonga_at_ucdavis.edu

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).
Received on Fri Feb 02 2001 - 16:40:41 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US