How to get the current (default) transaction isolation level in oracle?
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.
-- 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