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

Try hinting rule-based optimisation,
then first_rows in the query. This might solve the problem.

There are lots of odd little utitilities around that get thrown completely when the optimiser mode is not rule (or is something other than that used when the utility was created).

I came across a (very expensive) backup suite recently that required the database to be running in rule mode, otherwise the script to get the list of tablespace and files to backup took about 1.5 hours to run.

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

jgarry_at_my-dejanews.com wrote in message <7hhjd4$cua$1_at_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
>
Received on Fri May 14 1999 - 12:05:42 CDT