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@xxxxxxxxx>:
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
