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: Mohammad Rafiq <rafiq9857_at_hotmail.com>
Date: Sun, 04 Feb 2001 11:19:18 -0800
Message-ID: <F001.002AA2C6.20010204105023@fatcity.com>

Babu,
Unfortunately,I don't have any such script. Here it is design problem which is resulting in such locking.
Is this customized code or some third party product? In present days, normally 'for update nowait' is not being used. I remember with version 5 of Oracle, no row level locking was available and you were bound to use 'for update'clause to lock specific rows. Only SQL*FORMS were locking rows at that time. Please try to remove it in your test and see if your problem is resolved....You my find some trace files in your udump directory indicating LOCKING PROBLEM in your application(no entry in alertSID.log though).

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 14:36:55 -0800

Rafiq,

Thanks for the reply. This displays all the users who are accessing the table, and not giving me the userId who is accessing my row. There are plenty of users accessing the table in the production environment, it is important for me to find the user who is accessing my row (row 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:32 PM To: Multiple recipients of list ORACLE-L

Babu,

The message "Resource busy' is coming when your code is trying to change definition of table or trying to create any index when table is in use not for dml but may be somebody running select only.You may use following script

to check which user is accessing this table in case if any dml is invloved.....

set linesize 120
select substr(a.os_user_name,1,8) "OS User"
, substr(b.object_name,1,30) "Object Name"
, substr(b.object_type,1,8) "Type"
, substr(c.segment_name,1,10) "RBS"
, e.process "PROCESS"
, substr(d.used_urec,1,8) "# of Records"
from v$locked_object a
, dba_objects b
, dba_rollback_segs c
, v$transaction d
, v$session e

where a.object_id = b.object_id

and a.xidusn = c.segment_id
and a.xidusn = d.xidusn
and a.xidslot = d.xidslot
and d.addr = e.taddr

/

Regards
Rafiq

To: 'Mohammad Rafiq' <rafiq9857_at_hotmail.com>, ORACLE-L_at_fatcity.com Date: Fri, 2 Feb 2001 12:17:31 -0800

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

_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com

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

_________________________________________________________________
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).
Received on Sun Feb 04 2001 - 13:19:18 CST

Original text of this message

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