RE: Control File locks

No, it's not a problem, it's just a matter of scientific interest. Your query looks very good, I like it. Who is Steve that you have mentioned in your post?

> -----Original Message-----
> From: Joan Hsieh [mailto:joan.hsieh_at_tufts.edu]
> Sent: Tuesday, February 26, 2002 4:12 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: Control File locks
>
>
> Last year when I worked at a internet company. We had same problem.
> CF
> constantly get locked. Steve asked me to run this query. I am not sure
> it
> will help you or not? But at least it give you some idea which sql
> caused the lock. It was a mystery for us, never get solved.
> By the way,
> do you have st lock problem?
>
> Joan
>
> column resource format a8
> column sid format a4 justify right
> column sql_text format a38 wor
> break on resource
>
> select /*+ rule */
> l.type || '-' || l.id1 || '-' || l.id2 "RESOURCE",
> nvl(b.name, lpad(to_char(l.sid), 4)) sid,
> decode(
> l.lmode,
> 1, ' N',
> 2, ' SS',
> 3, ' SX',
> 4, ' S',
> 5, ' SSX',
> 6, ' X'
> ) holding,
> decode(
> l.request,
> 1, ' N',
> 2, ' SS',
> 3, ' SX',
> 4, ' S',
> 5, ' SSX',
> 6, ' X'
> ) wanting,
> l.ctime seconds,
> q.sql_text
> from
> sys.v_$lock l,
> sys.v_$session s,
> sys.v_$bgprocess b,
> sys.v_$sql q
> where
> l.type in ('CF', 'ST') and
> s.sid = l.sid and
> b.paddr (+) = s.paddr and
> q.address (+) = s.sql_address
> order by
> l.type || '-' || l.id1 || '-' || l.id2,
> sign(l.request),
> l.ctime desc
> /
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Joan Hsieh
> INET: joan.hsieh_at_tufts.edu
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Gogala, Mladen
  INET: MGogala_at_oxhp.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).