joining these two queries

[m...@pixar.com]

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;

which produce these two results:

STATUS SID SERIAL# INST_ID
-------- --- ------- -------
INACTIVE 118 58510 3
ACTIVE 123 58358 3
INACTIVE 125 47325 3

STATUS SID SERIAL# INST_ID START_DATE
-------- --- ---------- ------- -------------------
INACTIVE 125 47325 3 2009/06/17 00:42:25

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

STATUS SID SERIAL# INST_ID START_DATE
-------- --- ---------- ------- -------------------
INACTIVE 118 58510 3 --
ACTIVE 123 58358 3 2009/06/17 00:42:25
INACTIVE 125 47325 3 --

Many TIA!
Mark

--
Mark Harrison
Pixar Animation Studios

[Maxim Demenko]

m...@pixar.com schrieb:

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'
;

Best regards

Maxim

--
Why make things difficult, when it is possible to make them cryptic
and totally illogical, with just a little bit more effort?

Aksel Peter Jï¿œrgensen

[Shakespeare]

m...@pixar.com schreef:

Looks like you need an outer join on SID and a NVL on START_DATE
and I think your start_date in the third query is in the wrong place and
should have been in row 3.

Shakespeare

[Shakespeare]

Maxim Demenko schreef:

Sorry Maxim,

forgot to scroll down before posting a reply ;-)

Shakespeare

[Maxim Demenko]

Shakespeare schrieb:

> Maxim Demenko schreef:
>> m...@pixar.com schrieb:
>

> Sorry Maxim,
>
> forgot to scroll down before posting a reply ;-)
>
> Shakespeare

No, problem, happens me often enough too...

[m...@pixar.com]

Maxim Demenko <mdem...@gmail.com> wrote:
> I guess, you should read about *outer* joins (smth. like this should work)

Yes indeed, that's exactly what I needed... thanks!

[Peter Nilsson]

On Jun 17, 7:37 pm, Maxim Demenko <mdeme...@gmail.com> wrote:
> m...@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