Check active transaction with SID and Username in Oracle
V$TRANSACTION view help in search for the transaction which is actually executing or waiting for the resources in the Oracle Database system. When you are working with DML or Begin-End statements, you can confirm the details of a ransaction by querying from the V$TRANSACTION view.
Check the Active Transaction in Oracle Database
select * from v$transaction;
Check the Username and SID of Active Transaction in Oracle
col username format a8
col osuser format a8
col start_time format a17
col status format a12
tti 'Active transactions'
select s.sid,username,t.start_time, r.name, t.used_ublk "USED BLKS",
decode(t.space, 'YES', 'SPACE TX',
decode(t.recursive, 'YES', 'RECURSIVE TX',
decode(t.noundo, 'YES', 'NO UNDO TX', t.status)
)) status from sys.v_$transaction t, sys.v_$rollname r, sys.v_$session s
where t.xidusn = r.usn and t.ses_addr = s.saddr;
Example of Active Transaction
In Example, we are inserted data into a table EMP then hold for issue a COMMIT or ROLLBACK Statement,In mean time you can view active transaction information for the currently connected session as follows:
-- Insert the row into the table.
SQL> insert into emp values(1, 'John', 'Smith');
-- Check the active transaction from view.
SQL> select xidusn, xidsqn from v$transaction;
XIDUSN XIDSQN ---------- ---------- 9 369
-- Commit will make the changes permanent and close the transaction.
SQL> commit;
-- After commit means tranction is finished.
SQL> select xidusn, xidsqn from v$transaction;
no rows selected