Query to parallel dictionary views fails if where clause to complex oracle 8.1.6.0 on windows NT

From: Jesper Wolf Jespersen <jwj_at_dde.dk>
Date: Mon, 27 Nov 2000 00:28:42 +0100
Message-ID: <K1hU5.350$VE.8468@news.get2net.dk>

I am writing a sql script to help me identify which sessions in a parallel oracle are locking other sessions.

This script is very helpfull in determining who is responsible for some task not finishing, and has to give a total picture no matter how many instances are involved (usually one or two).

I started with some scripts written for Oracle 7 using the V$ views and have adapted these for Oracle 8 parallel server using the GV$ views. The script fails both on Oracle 8.0.4.x.x and 8.1.6.0.0 parallel servers.

The listing I bring here has the last line of the where clause commented out, in this incarnation the script rúns and gives meaningfull data, but when the last line is made active the script fails with "ORA-03113: filslut på kommunikationskanal". Sorry about the national language.

I considder this a bug in Oracle, but I need some way to get arround it. Do any of you experts have some advice on this. (Apart from not writing such a script).

• start cut from ana_lock.sql ----

select s.inst_id, s.sid sid, s.username uname, s.terminal,'DML' ltype,

  decode (l.lmode,1,'Null',
                  2,'Row-S',
                  3,'Row-X',
                  4,'Share',
                  5,'S/Row-X',
                  6,'Exclusive') lmode,
  decode (l.request,0,'No','Yes') blocked,

  u.name||'.'||o.name details
from gv$session s, gv$lock l, sys.obj$ o, sys.user$ u where

    s.inst_id   = l.inst_id
and s.sid       = l.sid
and l.type      = 'TM'
and l.id1       = o.obj#
and o.owner#    = u.user#(+)
and s.username is not null

union all
select s.inst_id, s.sid sid, s.username uname,s.terminal,

  decode (l.type,'TX','TX',
                 'UL','USR',
                      'SYS') ltype,
  decode (l.lmode,1,'Null',
                  2,'Row-S',
                  3,'Row-X',
                  4,'Share',
                  5,'S/Row-X',
                  6,'Exclusive') lmode,

  decode (l.request,0,'No','Yes') blocked,   decode (l.request,0,null,'Waiting on instance ' || b.inst_id || ' session '||to_char(b.sid)) details
from gv$session s, gv$lock l, gv$lock b
where

    s.inst_id = l.inst_id
and

    b.inst_id = s.inst_id
and

    l.request != 0
and

    s.sid = l.sid
and l.type != 'TM'
and l.id1 = b.id1(+)
and b.request(+) = 0

--and s.username is not null
order by 6 desc,4 desc,3,2,1
/

• end cut from ana_lock.sql ----

This is absolutely not the first time I see problems in writing scripts utilizing the dynamicall data dictionary views of parallel oracle. But this is the first time I have taken time out to pinpoint the problem.

I would like your comments on this.

Greetings from Denmark

Jesper Wolf Jespersen

--
+------------------------------+


| Jesper Wolf Jespersen        |

| Energi Udvikling             |

| eHuset                       |

| 14, Vesterlundsvej           |

| DK-2730 Herlev               |

| Denmark                      |

| Email: jwj_at_ehuset.com        |

| Tel  :  +45 44 57 20 00      |

| Dir  :  +45 44 57 23 15      |

| Fax  :  +45 44 57 20 01      |

| Web  : http://www.ehuset.com |

+------------------------------+

Received on Sun Nov 26 2000 - 17:28:42 CST