Menu
How can I see open transactions in Oracle ? I'm doing a custom performance tool, and I'd like to see the number of currently open transactions. Is there a SQL request to do that, or another way to gather this information ?
857
The dba_2pc_pending view contains an ADVISE column that directs the database to either commit or roll back the pending item. For related details, see my notes on dba_2pc_pending. You can use the ALTER SESSION ADVISE syntax to direct the two phase commit (2PC) mechanism.
Answers. You would need to query V$SESSION for your session to see the sql the session has used. Or you could use the sql monitor that is part of sql developer. Alternatively, if you have access to the SYS or SYSTEM users, or privileges on the V$ views, you could also try something like...
I use the query SELECT USED_UBLK FROM V$TRANSACTION to see how much undo blocks the transaction has allocated to it's changes. If the figure drops, then this is an indication that the transaction is rolling back.
1 - V$session is used on standalone database, an gv$session (g=global) is used mostly on RAC environments, showing all sessions on each node.
Use view v$transaction
144
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
