Re: How to get the sid from

From: Eriovaldo Andrietta <ecandrietta_at_gmail.com>
Date: Mon, 7 Aug 2017 10:27:16 -0300
Message-ID: <CAJdDhaOZUyk0+D_4v_tQUKCDCyWnGap+Q1zcD9xX28dWLG70Qg_at_mail.gmail.com>

Thanks Gogala.

I got success looking for active sessions : select s.inst_id

     ,s.sid
     ,s.username
     ,s.machine
     ,substr(s.program,1,25) program
     ,substr(s.module,1,25) module
     ,substr(s.status,1,1) st
     ,s.logon_time
     ,t.start_date
     ,t.used_ublk
     , t.*
from   gv$session     s
     ,gv$transaction t

where t.inst_id = s.inst_id
and t.ADDR = s.TADDR
order by t.used_ublk desc;

I will try retrieve data when session is not active using gv$ACTIVE_SESSION_HISTORY as you said.

Regards
Eriovaldo

Regards;

2017-08-06 0:25 GMT-03:00 Mladen Gogala <gogala.mladen_at_gmail.com>:

> Now that is simple:
>
> 1) There is V$TRANSACTION table which looks like this:
>
> SQL> desc v$transaction
> Name Null? Type
> ----------------------------------------- --------
> ----------------------------
> ADDR RAW(8)
> XIDUSN NUMBER
> XIDSLOT NUMBER
> XIDSQN NUMBER
>
> ....
>
> In your case, XIDUSN=10,XIDSLOT=50 and XIDSQN=163945. That is the
> structure of local_tran_id='10.50.1635945'. That allows you to get the
> transaction address. V$SESSION table has "TADDR" column which corresponds
> to the ADDR column in V$TRANSACTION table. That is only usable if the
> session that has issued the transaction is still active. You can also get
> XID column from V$TRANSACTION table, which corresponds to the XID column in
> V$ACTIVE_SESSION_HISTORY. There is a catch: you need a license to use
> V$ACTIVE_SESSION_HISTORY. You need performance tuning and diagnostic pack
> licenses.
>
> Regards
>
> On 08/05/2017 07:33 AM, Eriovaldo Andrietta wrote:
>
> Hello,
>
> I got an error when running this update:
>
> SQL> update col_event set state =0 where state =4 and id =463920006;
> update col_event set state =0 where state =4 and id =463920006
> ORA-01591: bloqueio retido pela transação distribuída 10.50.1635945
>
> Looking at the result of this query :
>
> select * from dba_2pc_pending where local_tran_id='10.50.1635945';
>
> The dba_2pc_pending contains the column COMMIT# 11345312359643
>
> How can I get the sid, sql_id and also identify the content of this
> transaction in order to make decision for COMMIT or ROLLBACK forced.
>
> Is there a way to identify it ?
>
> Regards
> Eriovaldo
>
>
>
>
>
>
>
>
>
>
> --
> Mladen Gogala
> Oracle DBA
> Tel: (347) 321-1217
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Aug 07 2017 - 15:27:16 CEST