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.