10

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

6

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.

6

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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