Re: joining these two queries

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Wed, 17 Jun 2009 21:37:12 +0200
Message-ID: <4a3945e9$0$194$e4fe514c_at_news.xs4all.nl>

Maxim Demenko schreef:

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

Sorry Maxim,

forgot to scroll down before posting a reply ;-)

Shakespeare Received on Wed Jun 17 2009 - 14:37:12 CDT