Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
add comment

2 Answers

-- 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
add comment

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))
      WHEN 0 THEN 'READ COMMITTED'
      ELSE 'SERIALIZABLE'
   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:

declare 
   trans_id Varchar2(100);
begin
   trans_id := dbms_transaction.local_transaction_id( TRUE );
end;
/

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
add comment

Your Answer

 
discard

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.