Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

How to get the current (default) transaction isolation level in oracle?

share|improve this question
If you're looking for the current isolation level of a session, see this SO question: How can you see what transaction isolation level an arbitrary oracle session is using. – Vincent Malgrat Apr 14 '11 at 15:34
Are you looking for the default isolation level for transactions in a session or the isolation level of the current transaction in progress? – Leigh Riffel Apr 14 '11 at 19:55

Using the query from the SO answer Vincent Malgrat referenced, here is how you can get the transaction isolation level for the transaction in progress:

SELECT s.sid, s.serial#,
   CASE BITAND(t.flag, POWER(2, 28))
   END AS isolation_level
FROM v$transaction t 
JOIN v$session s ON t.addr = s.taddr AND s.sid = sys_context('USERENV', 'SID');

If you are not already in a transaction you can start one with the following:

   trans_id Varchar2(100);
   trans_id := dbms_transaction.local_transaction_id( TRUE );

It seems like there would be an easier way than this. I don't know how to get the default isolation level for the session if that is what you are looking for.

share|improve this answer

-- I am using something like below

with q1 as ( select distinct name, isdefault, value ,decode(value,'serializable',SID,null) SID from V$SES_OPTIMIZER_ENV where lower(name) like '%isolation%' order by name ) select q1.* ,vs.status,vs.username,vs.OSUSER,vs.MACHINE,vs.TERMINAL,vs.PROGRAM from q1, v$session vs where q1.sid=vs.sid(+);

-- Regards. -- AZ

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.