Re: joining these two queries

From: Peter Nilsson <airia_at_acay.com.au>
Date: Sun, 21 Jun 2009 22:38:33 -0700 (PDT)
Message-ID: <30df6651-cf3f-476d-8ce2-c0533143dea3_at_z20g2000prh.googlegroups.com>

On Jun 17, 7:37 pm, Maxim Demenko <mdeme..._at_gmail.com> wrote:
> m..._at_pixar.com schrieb:
> > I have these two queries:
> >
> > -- active, inactive sessions on instance 3
> > select status,sid,serial#,inst_id
> >  from gv$session
> > where inst_id=3 and username <> 'SYS';
> >
> > -- sessions with open transaction on instance 3
> > select s.status,s.sid,s.serial#,s.inst_id,t.start_date
> >   from gv$transaction t, dba_rollback_segs r, gv$session s
> > where  t.xidusn=r.segment_id and
> >        s.saddr=t.ses_addr and
> >        t.inst_id=3 and
> >        s.inst_id=t.inst_id;

<snip>
> > But I can't figure out how to join these into a
> > single query like this... Can somebody kindly loan me
> > a clue?

<snip>

>

> I guess, you should read about *outer* joins (smth. like this
> should work)

>

> select s.status, s.sid, s.serial#, s.inst_id, t.start_date
>    from gv$transaction t, dba_rollback_segs r, gv$session s
>   where t.xidusn = r.segment_id(+)
>     and s.saddr = t.ses_addr(+)
>     and t.inst_id(+) = 3
>     and s.inst_id = t.inst_id(+)
>     and s.username <> 'SYS'
> ;

[I know jack about gv$xxx and dba_rollback_segs, so perhaps I'm missing some meta-clues, but...]

That's doing an outer join between r and t, whereas the original second
query was an inner join. So, perhaps something more like...

  select s.status, s.sid, s.serial#, s.inst_id, t.start_date     from gv$session s
    left join (select t.ses_addr, t.inst_id, t.start_date

                 from gv$transaction t
                 join dba_rollback_segs r
                        on t.xidusn = r.segment_id) t
             on t.ses_addr = s.saddr
            and t.inst_id = s.inst_id

  where s.inst_id = 3
    and s.username <> 'SYS'

--
Peter

Received on Mon Jun 22 2009 - 00:38:33 CDT