How to Check Active Transaction in Oracle Database – v_$transaction

advertisements

_____________________________________________________________________________________________________________________

V_$transaction table will give you the active transaction in the database. This dynamic view get recorded with the active transaction which is being executed or waiting for the resources in the database or operating system. For example, suppose you have an uncommitted transaction in your database it will show up in this particular view. 

SQL Query

set pages 1000 lines 102
col osuser format a10
col start_time format a20
col username format a10
col status format a15
ttitle 'Database Active transactions'

select sess.sid,username,trans.start_time, r.name, trans.used_ublk "USED BLKS",
decode(trans.space, 'YES', 'SPACE TX',
decode(trans.recursive, 'YES', 'RECURSIVE TX',
decode(trans.noundo, 'YES', 'NO UNDO TX', trans.status)
)) status from sys.v_$transaction trans, sys.v_$rollname r, sys.v_$session sess
where trans.xidusn = r.usn and trans.ses_addr = sess.saddr;

advertisements

 

Sample Output

SQL> set pages 1000 lines 102
SQL> col osuser format a10
SQL> col start_time format a20
SQL> col username format a10
SQL> col status format a15
SQL> ttitle 'Database Active transactions'
SQL>
SQL> select sess.sid,username,trans.start_time, r.name, trans.used_ublk "USED BLKS",
  decode(trans.space, 'YES', 'SPACE TX',
  decode(trans.recursive, 'YES', 'RECURSIVE TX',
  decode(trans.noundo, 'YES', 'NO UNDO TX', trans.status)
  )) status from sys.v_$transaction trans, sys.v_$rollname r, sys.v_$session sess
  where trans.xidusn = r.usn and trans.ses_addr = sess.saddr;

Fri Mar 26                                                                                   page    1
                                     Database Active transactions

       SID USERNAME   START_TIME           NAME                            USED BLKS STATUS
---------- ---------- -------------------- ------------------------------ ---------- ---------------
     10436            03/26/21 05:35:09    _SYSSMU1380_2805788569$                 1 ACTIVE

_____________________________________________________________________________________________________________________

Website Stats