Re: Control File locks

From: Joan Hsieh <joan.hsieh_at_tufts.edu>
Date: Tue, 26 Feb 2002 13:12:11 -0800
Message-ID: <F001.004191AC.20020226131211@fatcity.com>

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). Received on Tue Feb 26 2002 - 15:12:11 CST