Oracle Database - How to view Current Transaction Status (ROLLBACK or ONGOING)

One of the key Questions in Oracle Database is how to find out the current Transaction Status in Oracle i.e it is in rollback / Ongoing ?

Well if we look at it oracle provides view V$transaction which gives us a flag column which helps  us (PSSS.. Undocumented but figured out with some testing)

Here is the clue
V$transaction.FLAG = 7683  - ONGOING
V$transaction.FLAG = 7811  - ROLLBACK.

Below SQL is what can help you.
I have also taken liberty to add couple of columns and another view v$sqlcommand

col username for a15
col tr_status for a15
col COMMAND_NAME for a20

select ss.sid, ss.serial#, ss.username, st.used_ublk, st.used_urec, ss.status, decode(st.flag,7683,'ONGOING',7811,'ROLLBACK', st.flag) tr_status,  sqt.command_name, ss.sql_id, ss.prev_sql_id
from v$session ss , v$transaction st, V$sqlcommand sqt
where ss.saddr = st.ses_addr
and sqt.command_type = ss.command
order by 3;

The additional columns are to give you some further picture, for example if the status of session is INACTIVE and transaction is ONGOING that means, the session did some dmls but did not commit yet but sitting idle.


Below is a test I did on my dummy database to check the values.
All I did was to insert multiple times into my dummy table.

I kept on doing inserts- to my dummy table and let me show you the output.
(The output might be a bit off bit due to font limitation, but the attached picture will give you the idea what I am talking about )



When you see the rollback happening, it is actually when I did the rollback and no rows selected comes when the rollback is complete.

SPECIAL CASE

A special case for this is that if you have killed a session, in this case you will not able to see any data using above query.

You can see these details use V$FAST_START_TRANSACTIONS