How can you see what transaction isolation level an arbitrary oracle session is using

oracle transactions oracle10g isolation-level
kon5ad picture kon5ad · Sep 8, 2010 · Viewed 10.2k times · Source

I am trying to find out what isolation level a particular session (not my own) has on an oracle server. Is there a v$.. view to get this?

Answer

Danilo Piazzalunga picture Danilo Piazzalunga · Jan 15, 2011

You can test bit 28 in the flag column in v$transaction[1].

SELECT s.sid, s.serial#,
  CASE BITAND(t.flag, POWER(2, 28))
    WHEN 0 THEN 'READ COMMITTED'
    ELSE 'SERIALIZABLE'
  END AS isolation_level
FROM v$transaction t, v$session s
WHERE t.addr = s.taddr
  AND s.sid = :sid
  AND s.serial# = :serial;

Just remember that v$transaction only lists active transactions[2]; for example, you need to issue an insert/update/delete/merge, or use "for update"[3].

Related questions

What is the difference between Non-Repeatable Read and Phantom Read?

What is the difference between non-repeatable read and phantom read? I have read the Isolation (database systems) article from Wikipedia, but I have a few doubts. In the below example, what will happen: the non-repeatable read and phantom read? Transaction …

Read More
Do DDL statements always give you an implicit commit, or can you get an implicit rollback?

If you're halfway through a transaction and perform a DDL statement, such as truncating a table, then the transaction commits. I was wondering whether this was always the case and by definition, or is there a setting hidden somewhere that …

Read More
Error on Oracle while using DBLINK

I am Using jboss5.1.x, EJB3.0 ,JPA3. I am trying to do 'select' query from view which is connected via dblink to another database. source database is Oracle 9, destination dabatase is Oracle 8. I am getting this error: 15:27:06,625 WARN [JDBCExceptionReporter] SQL …

Read More