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

Jesper,

We had a similar problem in 8.1.6.2.

Have you got star_transformation_enabled = true or temp_disable, and compatible set to something higher than 8.0.4?

If so you may need to set star_transformation_enabled to false. Alternatively it is fixed in 8.1.7

Regards

Tracey

In article <K1hU5.350$VE.8468_at_news.get2net.dk>,   "Jesper Wolf Jespersen" <jwj_at_dde.dk> wrote:
> 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 |
> +------------------------------+
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Nov 28 2000 - 07:06:52 CST