Re: joining these two queries

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Wed, 17 Jun 2009 11:37:32 +0200
Message-ID: <4A38B95C.6030007_at_gmail.com>

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

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

Received on Wed Jun 17 2009 - 04:37:32 CDT