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$transaction and make a selection of
For example, through the console and SQL*Plus as two columns – session ID and username.
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
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.