join of dba_lock and v$session goes to lalaland

From: <jgarry_at_my-dejanews.com>
Date: Fri, 14 May 1999 16:35:49 GMT
Message-ID: <7hhjd4$cua$1@nnrp1.deja.com>

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 - 11:35:49 CDT