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

Home -> Community -> Usenet -> c.d.o.server -> Re: locking

Re: locking

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 09 Jun 2001 15:19:00 -0400
Message-ID: <d4t4itg2g08g838i671vvmr7d601cc7jki@4ax.com>

A copy of this was sent to Salnik Andrej <d95014_at_htlwrn.ac.at> (if that email address didn't require changing) On Sat, 09 Jun 2001 14:18:11 GMT, you wrote:

>how can i display which rows / tables are locked?
>(sp_lock MS SQL-Server)
>
>

rows -- no go. We don't keep a list of what rows are locked. You would have to visit each and every block of the table to figure that out.

That would be a bad implementation (would demand a serial lock manager, limits on the number of locks and cause locks to be a scarce resource to be managed and conserved)

for people who have rows locked in a table, something like:

 1 select (select username from v$session where sid = l.sid ) who,

  2                           (select owner||'.'||object_name from dba_objects
  3                              where object_id = l.id1 ) what
  4 from v$lock l
  5* where type = 'TM'
ops$tkyte_at_ORA8I.WORLD> /
WHO                            WHAT
------------------------------ ------------------------------
SCOTT                          SCOTT.EMP


works. that shows that scott has some rows locked in th escott.emp table

(rewrite the query differently in 8.0 and before since it didn't do the select of a select like that -- since no one every posts the version they are interested in i assume the latest....)

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://asktom.oracle.com/ http://asktom.oracle.com/~tkyte/
Oracle Magazine: http://www.oracle.com/oramag

Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  

Opinions are mine and do not necessarily reflect those of Oracle Corp 
Received on Sat Jun 09 2001 - 14:19:00 CDT

Original text of this message

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