jian huang -- Thanks for the question regarding "About the transaction status in oracle 9i", version 9.2.0

You Asked

Hello Tom

Thanks for your time..

I am using oracle 9i(9.2.0.1) databases. And some day I find there is a column (status) in the v$transaction and I understand if there is a transaction open and not committed, the status always shows active. If there is a commit or rollback in the session, the row is gone in the v$transaction. So I wonder if there is under any case the different state(such as inactive,I am not sure) that will show in this view---v$transaction. If so,would you please give a example?


Thanks again..You are always very helpful..

and we said...

using v$fixed_view_definition, we can see the inclusive list of possible return values:

ops$tkyte%ORA9IR2> select * from v$fixed_view_definition where view_name = 
'GV$TRANSACTION';

VIEW_NAME
------------------------------
VIEW_DEFINITION
-------------------------------------------------------------------------------
GV$TRANSACTION
select inst_id,ktcxbxba,kxidusn,kxidslt,kxidsqn,ktcxbkfn,kubablk, kubaseq,kubar
ec, decode(ktcxbsta,0,'IDLE',1,'COLLECTING',2,'PREPARED',3,'COMMITTED',
         4,'HEURISTIC ABORT',5,'HEURISTIC COMMIT',                 6,'HEURISTIC
 DAMAGE',7,'TIMEOUT',9,'INACTIVE',                 10,'ACTIVE',11,'PTX PREPARED
',12,'PTX COMMITTED',                 'UNKNOWN'), ktcxbstm,ktcxbssb,ktcxbssw, k
tcxbsen,ktcxbsfl,ktcxbsbk,ktcxbssq,ktcxbsrc, ktcxbses,ktcxbflg, decode(bitand(k
tcxbflg,16),0,'NO','YES'), decode(bitand(ktcxbflg,32),0,'NO','YES'), decode(bit
and(ktcxbflg,64),0,'NO','YES'), decode(bitand(ktcxbflg,8388608),0,'NO','YES'),
ktcxbnam, ktcxbpus,ktcxbpsl,ktcxbpsq, ktcxbpxu,ktcxbpxs,ktcxbpxq, ktcxbdsb, ktc
xbdsw, ktcxbubk,ktcxburc,ktcxblio,ktcxbpio,ktcxbcrg,ktcxbcrc from x$ktcxb where
 bitand(ksspaflg,1)!=0 and bitand(ktcxbflg,2)!=0

Most all of them have to do with a two phase commit process.

Reviews

Very Helpful!   April 17, 2008 - 11am Central time zone Bookmark | Bottom | Top Reviewer: Jian Huang zheng from China The definition demonstrates well. So can I assume that if not being related to two phase commit process, I mean there is no distributed or remote commit(rollback) process, only *active* will show in the view? Actually I am interested in the inactive status, since I can see active all the time.. Thanks! Followup   April 17, 2008 - 4pm Central time zone: I'm not aware of the conditions that would make it "idle" or "inactive" Thanks!   April 18, 2008 - 12am Central time zone Bookmark | Bottom | Top Reviewer: jian huang zheng from China Thanks,and I got it. Helpful always..

---

Write a Review