| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: What row and table are locked
Elisa Cotrina wrote:
> > Mike Moritz wrote: > > > > I am running Oracle Server 7.1.4.1.0 > > > > Can someone help me determine what rows of what tables users are > > locking? If I am way off base here, does anyone have some scripts > > that will provide the desired info. > > > > I've figured out how to determine which users have locks but am > > not able to determine what table and row they are locking. > > > > I think the solution lies in the ability to decode the *ADDR > > fields of v$_lock. Does one of the *ADDR point to a ROWID? > > Am I close? > > > > Can anyone tell me what these fields point to and how to decode > > them? > > > > SQL> describe v$_lock; > > Name Null? Type > > ---------------- -------- ---- > > LADDR RAW(4) > > KADDR RAW(4) > > SADDR RAW(4) > > RADDR RAW(4) > > LMODE NUMBER > > REQUEST NUMBER > > > > For me the easier way to see what row is locked, who lock and who is > locked is: > > select oracle_username from v$locked_object where xidusn is not null; > > Return who lock; > > select v$session.username, v$session.row_wait_row#, > v$sesion.row_wait_file#, v$session.row_wait_block#, > all_objects.object_name from v$session, > all_objects where all_objects.object_id = v$session.row_wait_obj#; > > Return who is locked and what object is locked. > > And the rowid of the lock row is: > > row_wait_block#, row_wait_row#, row_wait_file# (you must change the > format only); > > Hope this help: > > Elisa
Elisa:
Here is a script that I picked up somewhere, (IOUG I think) that will tell you who is holding what locks and on what they are being held.
Hope this helps,
John Hough
cosdev$ cat locks.sql
rem $TOOLS/locks.sql
rem
rem Displays locking information for the current ORACLE_SID database
rem
set termout off
set heading off
col dbname1 new_value dbname noprint
select name dbname1 from v$database;
set termout on
set heading on
set pagesize 9999
column osuser format a14 heading "-----O/S------|Username Pid"
column username format a17 heading "-----ORACLE-----|Username ID Ser"
column locktype format a10 heading "Type"
column held format a9 heading "Lock Held"
column object_name format a15 heading "Object Name" wrap
column request format a9 heading " Lock|Requested"
column id1 format 999999
column id2 format 9999
ttitle center 'Lock report for the ' &&dbname ' database' skip 2
select rpad(osuser, 9)||lpad(p.spid, 5) osuser,
rpad(s.username,8)||lpad(s.sid, 4)||lpad(s.serial#, 5) username,
decode(l.type,
'MR', 'Media Reco',
'RT', 'Redo Thred',
'UN', 'User Name',
'TX', 'Trans',
'TM', 'DML',
'UL', 'PL/SQL Usr',
'DX', 'Dist. Tran',
'CF', 'Cntrl File',
'IS', 'Inst State',
'FS', 'File Set',
'IR', 'Inst Reco',
'ST', 'Disk Space',
'TS', 'Temp Seg',
'IV', 'Cache Inv',
'LS', 'Log Switch',
'RW', 'Row Wait',
'SQ', 'Seq Number',
'TE', 'Extend Tbl',
'TT', 'Temp Table',
l.type) locktype,
' ' object_name,
decode(lmode,1,Null,2,'Row Share',3,'Row Excl',4,'Share',
5,'Sh Row Ex',6,'Exclusive',' ') held,
decode(request,1,Null,2,'Row Share',3,'Row Excl',4,'Share',
5,'Sh Row Ex',6,'Exclusive',' ') request
from v$lock l, v$session s, v$process p
where s.sid = l.sid and
s.username <> ' ' and
s.paddr = p.addr and
l.type <> 'TM' and
(l.type <> 'TX' or l.type = 'TX' and l.lmode <> 6)
union
rpad(s.username,8)||lpad(s.sid, 4)||lpad(s.serial#, 5) username,
decode(l.type,
'MR', 'Media Reco',
'RT', 'Redo Thred',
'UN', 'User Name',
'TX', 'Trans',
'TM', 'DML',
'UL', 'PL/SQL Usr',
'DX', 'Dist. Tran',
'CF', 'Cntrl File',
'IS', 'Inst State',
'FS', 'File Set',
'IR', 'Inst Reco',
'ST', 'Disk Space',
'TS', 'Temp Seg',
'IV', 'Cache Inv',
'LS', 'Log Switch',
'RW', 'Row Wait',
'SQ', 'Seq Number',
'TE', 'Extend Tbl',
'TT', 'Temp Table',
l.type) locktype,
object_name,
decode(lmode,1,NULL,2,'Row Share',3,'Row Excl',4,'Share',
5,'Sh Row Ex',6,'Exclusive',NULL) held,
decode(request,1,NULL,2,'Row Share',3,'Row Excl',4,'Share',
5,'Sh Row Ex',6,'Exclusive',NULL) request
from v$lock l, v$session s, v$process p, sys.dba_objects o
where s.sid = l.sid and
o.object_id = l.id1 and
l.type = 'TM' and
s.username <> ' ' and
s.paddr = p.addr
rpad(s.username,8)||lpad(s.sid, 4)||lpad(s.serial#, 5) username,
decode(l.type,
'MR', 'Media Reco',
'RT', 'Redo Thred',
'UN', 'User Name',
'TX', 'Trans',
'TM', 'DML',
'UL', 'PL/SQL Usr',
'DX', 'Dist. Tran',
'CF', 'Cntrl File',
'IS', 'Inst State',
'FS', 'File Set',
'IR', 'Inst Reco',
'ST', 'Disk Space',
'TS', 'Temp Seg',
'IV', 'Cache Inv',
'LS', 'Log Switch',
'RW', 'Row Wait',
'SQ', 'Seq Number',
'TE', 'Extend Tbl',
'TT', 'Temp Table',
l.type) locktype,
'(Rollback='||rtrim(r.name)||')' object_name,
decode(lmode,1,NULL,2,'Row Share',3,'Row Excl',4,'Share',
5,'Sh Row Ex',6,'Exclusive',NULL) held,
decode(request,1,NULL,2,'Row Share',3,'Row Excl',4,'Share',
5,'Sh Row Ex',6,'Exclusive',NULL) request
from v$lock l, v$session s, v$process p, v$rollname r
where s.sid = l.sid and
l.type = 'TX' and
l.lmode = 6 and
trunc(l.id1/65536) = r.usn and
s.username <> ' ' and
s.paddr = p.addr
order by 5, 6;
SQL*Plus: Release 3.1.3.7.1 - Production on Mon Mar 24 09:31:02 1997
Copyright (c) Oracle Corporation 1979, 1994. All rights reserved.
Enter user-name:
Connected to:
Oracle7 Server Release 7.1.6.2.0 - Production Release
With the distributed option
PL/SQL Release 2.1.6.2.0 - Production
Welcome to the cosdev/Oracle devl database
SQL> @locks
Lock report for the DEVL database
-----O/S------ -----ORACLE-----
Lock
Username Pid Username ID Ser Type Object Name Lock Held
Requested
-------------- ----------------- ---------- --------------- ---------
---------
t4z 61060 TBMS_USE 20 367 Trans (Rollback=R2_04 Exclusive
)
t4z 61060 TBMS_USE 20 367 DML TBMSUACT Row Excl
Received on Mon Mar 24 1997 - 00:00:00 CST
 |
 |