From: "david spaisman" Newsgroups: comp.databases.oracle.server Subject: Re: which row is locked? Date: Tue, 12 Dec 2000 17:59:29 -0500 Organization: Compaq Computer Corp. Lines: 99 Message-ID: <916ag9$ts$1@mailint03.im.hou.compaq.com> References: <912q5k$5in$1@mailint03.im.hou.compaq.com> <9130lo$h2r$1@nnrp1.deja.com> <913a1r$pf7$1@nnrp1.deja.com> <913hs8$9qq$1@mailint03.im.hou.compaq.com> <91446q$fp7$1@nnrp1.deja.com> NNTP-Posting-Host: 16.69.11.25 X-Trace: mailint03.im.hou.compaq.com 976661833 956 16.69.11.25 (12 Dec 2000 22:57:13 GMT) X-Complaints-To: usenet@mailint03.im.hou.compaq.com NNTP-Posting-Date: 12 Dec 2000 22:57:13 GMT X-Newsreader: Microsoft Outlook Express 4.72.3155.0 X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3155.0 Mark: Thanks for the subsequent reply. DAvid Mark D Powell wrote in message <91446q$fp7$1@nnrp1.deja.com>... >In article <913hs8$9qq$1@mailint03.im.hou.compaq.com>, > "david spaisman" wrote: >> Mark: >> >> So which data dictionary table would I need to query to get thedata block >> transaction work area >> when there is no waiting on locked rows? >> >> Any help would be appreciated. Thanks. >> >> David >> >> Mark D Powell wrote in message <913a1r$pf7$1@nnrp1.deja.com>... >> >In article <9130lo$h2r$1@nnrp1.deja.com>, >> > steveee_ca@my-deja.com wrote: >> >> Hi David, >> >> >> >> If you're trying to find which row is causing contention, you can use >> >> V$SESSION..look at these columns: >> >> >> >> ROW_WAIT_OBJ# >> >> ROW_WAIT_FILE# >> >> ROW_WAIT_BLOCK# >> >> ROW_WAIT_ROW# >> >> >> >> I hope this helps. >> >> >> >> Steve >> >> >> >> In article <912q5k$5in$1@mailint03.im.hou.compaq.com>, >> >> "david spaisman" wrote: >> >> > Hello: >> >> > >> >> > The application I am working with runs on Oracle 8.1.6.00. >> >> > >> >> > I have seen several scripts which can tell me which object is locked by >> >> > user. What would be more helpful would be: >> >> > >> >> > how can I tell which row is locked by a particular process? >> >> > >> >> > Does any one know how I can tell which row for an object is locked by which >> >> > user? >> >> > >> >> > Any information you can provide will be greatly appreciated. Thanks. >> >> > >> >> > David Spaisman >> >> > >> >The v$session columns will contain rowid information only if the >> >session is waiting on a locked row. The columns will be empty if the >> >session is waiting on a resource. >> > >> >View v$lock will show all objects locked by a session, but there is no >> >place in the SGA where Oracle keeps row level lock information for all >> >row level locks held by the locking session. Oracle keeps row level >> >lock information in the Oracle data block transaction work area so the >> >only way to see if a row, which has no waiting sessions, is locked is >> >to query it. >> > >> >-- >> >Mark D. Powell -- The only advice that counts is the advice that >> > you follow so follow your own advice -- >> > > >As I said "Oracle keeps row level lock information in the Oracle data >block transaction work area so the only way to see if a row, which has >no waiting sessions, is locked is to query it." > >There is no dictionary or v$ table that you can query for this >information. You have to attempt to retrieve the row for update with >the nowait option and check the return code. This is not a practical >way to see what rows an application has locked except maybe under >limited circumstances. > >-- >Mark D. Powell -- The only advice that counts is the advice that > you follow so follow your own advice -- > > >Sent via Deja.com http://www.deja.com/ >Before you buy.