Re: join of dba_lock and v$session goes to lalaland

From: Vitaliy Mogilevskiy <vit100gain_at_earthlink.net>
Date: Fri, 14 May 1999 10:29:48 -0700
Message-ID: <373C5D8C.9FA42C1B@earthlink.net>

This is a nice script that will do it:

rem $TOOLS/locks.sql
rem
rem Displays locking information for the current ORACLE_SID database rem
rem Last Change 12/04/96 by Brian Lomasky 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
spool locks.lis
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
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',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

union
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,
'(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;
spool off

+-------------------------------------------------------------+
| Vitaliy Mogilevskiy                                         |
| Senior Consultant                                           |
| CORE Technology Group, Inc.                                 |
| E-mail:    vit100gain_at_earthlink.net                         |
| Fax :      (707) 516-2163                                   |
| Web Page:  http://home.earthlink.net/~vit100gain/index.html |
|            *** Free DBA Script Library at my Web Page ***   |
+-------------------------------------------------------------+

jgarry_at_my-dejanews.com wrote:

> I was trying a Tale From the Scrypt, and hung up on the following
> snippet:
>
> prompt  =========================
> prompt SYSTEM-WIDE LOCKS - all requests for locks or latches
> prompt  =========================
> prompt
> select substr(username,1,12) "User",
>           substr(lock_type,1,18) "Lock Type",
>                 substr(mode_held,1,18) "Mode Held"
>         from sys.dba_lock a, v$session b
>         where lock_type not in ('Media Recovery','Redo Thread')
>         and a.session_id = b.sid;
> prompt
>
> Trimming out any part of this, it works snappily enough (returns in
> seconds).  It just seems to be the combination of media recovery and
> redo thread.  Using <>'s instead of not in also has the problem.  Is
> there some obvious conflict I'm missing?  What problem might this be
> indicating?  Removing the join gives the cartesian in about 30 seconds,
> then seems to lock up.  A self-referential problem (trying to look at
> some lock while it is creating it)?
>
> 7.3.4 on hp-ux 10.20.
>
> jg
> --
> These opinions mine
> mailto:joel-garry_at_nospam.home.com                Remove nospam to mail
> http://ourworld.compuserve.com/homepages/joel_garry  Oracle & unix guy
>
> --== Sent via Deja.com http://www.deja.com/ ==--
> ---Share what you know. Learn what you don't.---

Received on Fri May 14 1999 - 12:29:48 CDT