sql – How to get information about database sessions that have open transactions?

Question:

How to get information about all database sessions that have open transactions from data dictionary views?

As far as I understand, you need to access v$session and v$transaction and make a selection of v$session.sid and v$session.username .

For example, through the console and SQL*Plus as two columns – session ID and username.

Can the v$transaction.flag=7811 constraint be used to detect unclosed transactions?

For example, like this:

SELECT v$session.sid, v$session.username
FROM v$session, v$transaction
WHERE v$transaction.flag=7811;

Version: Oracle Database 11g Release 2

Answer:

Try like this:

select s.sid, s.serial#, s.username
from v$transaction t
inner join v$session s on t.addr = s.taddr
; 

no rows selected

select 1 from t1 for update;

select s.sid, s.serial#, s.username
from v$transaction t
inner join v$session s on t.addr = s.taddr
;

       SID    SERIAL# USERNAME 
---------- ---------- ----------
        34      55338 OE      

Note that "unclosed transactions" does not mean that data has changed. In the example above, for example, the request with for update did not change anything.

Scroll to Top