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:[EMAIL PROTECTED]]
> 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: [EMAIL PROTECTED]
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> 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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
