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

_____________________________________________________________________________________________________________________

Live Traffic Feed

A visitor from Boydton viewed 'DBMS_SCHEDULER. Create_job Fails with ORA-06512 OR' 1 hr 32 mins ago

A visitor from Virginia viewed 'How Stop / Start Multiple Database Instances Under' 2 hrs 11 mins ago

A visitor from Hilton viewed 'RMAN-08591: WARNING: invalid archived log deletion' 2 hrs 13 mins ago

A visitor from Denver viewed 'Troubleshoot fix ORA-28368: cannot auto-create wal' 2 hrs 34 mins ago

A visitor from Delhi viewed 'RMAN Restore Failed RMAN-06100: no channel to rest' 2 hrs 45 mins ago

Real-time | Get Script | More Info