Check and set the isolation level in Oracle

Oracle support three transaction isolation levels:
1. Read Committed (Default)
It is the default oracle used, we does not support dirty read and uncommitted data read.Other user can change the data read by a query by other user.
Its guarantees that any data read is committed is able to read by another transaction. means not allowing dirty read. The transaction holds a read or write lock on the current row, and thus prevent other transactions from reading, updating or deleting it.

2. Serializable
In Serializable, transaction is executed in serial fashion, they will not face non-repeatable reads or phantoms problems.
A serialize execution is guaranteed to be serializable. Serializable execution is defined to be an execution of operations in which concurrently executing transactions appears to be serially executing.

3. Read Only
Read only transaction means that only access data that is already committed at time of transaction start be accessed.
It basically used to report the data from multiple table if some other user updated that data.
It used to maintain transaction-level read consistency.

Check the connection type from following query:


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;

      SID    SERIAL#  ISOLATION_LEVE
---------- ---------- --------------
       509      55279 READ COMMITTED

Set the isolation level at session level
Following command change isolation level at session level.

SQL> ALTER SESSION SET ISOLATION_LEVEL=SERIALIZABLE;
Session altered.

SQL> ALTER SESSION SET ISOLATION_LEVEL=READ COMMITTED;
Session altered.


Set the Transaction isolation level
Following are the example of setting the transaction level isolation in Oracle. It should be the first command as transaction and SYS user is not supported by command.

Set transaction level SERIALIZABLE

SQL> CONN TEST@XEPDB1
Enter password:
Connected.
SQL>
SQL> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE NAME 'TRAN1';
Transaction set.


Set transaction level READ COMMITED

SQL> SET TRANSACTION ISOLATION LEVEL READ COMMITTED NAME 'TRAN1';
Transaction set.


Set Transaction level READ ONLY

SET TRANSACTION READ ONLY NAME 'TRAN1';