Check the status of active transaction in Oracle database

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.