The SET TRANSACTION statement allows you to begin a read-only or read-write session,establish an isolation level,or assign the current transaction to a specified rollback segment or assign a name to the current transaction.
This statement must be the first SQL statement in a transaction,and it can appear only once.
SET TRANSACTION [ READ ONLY | READ WRITE ]
[ ISOLATION LEVEL [SERIALIZABLE | READ COMMITED ]
[ USE ROLLBACK SEGMENT ‘segment_name’ ]
[ NAME ‘transaction_name’ ];
SET TRANSACTION READ ONLY
This version makes the current transaction as read-only. In a read-only transaction,all subsequent queries see only those changes that were committed before the transaction began.This statement is useful when you are executing long-running, multiple query reports,and you want to make sure that the data used in the report is consistent.
Session 1 :-
SQL> SELECT * FROM emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 777 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1617 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1275 500 30
7566 JONES MANAGER 7839 02-APR-81 5205 20
7654 MARTIN SALESMAN 7698 28-SEP-81 20013 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2865 30
7782 CLARK MANAGER 7839 09-JUN-81 2487 10
7788 SCOTT ANALYST 7566 19-APR-87 3026 20
7839 KING PRESIDENT 17-NOV-81 5034 10
7844 TURNER SALESMAN 7698 08-SEP-81 1524 0 30
7876 ADAMS CLERK 7788 23-MAY-87 11000 20
7900 JAMES CLERK 7698 03-DEC-81 12000 30
7902 FORD ANALYST 7566 03-DEC-81 3029 20
7934 MILLER CLERK 7782 23-JAN-82 13000 10
14 rows selected.
SQL> UPDATE emp SET sal = 5 WHERE ename = 'SMITH';
1 row updated.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 5 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1617 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1275 500 30
7566 JONES MANAGER 7839 02-APR-81 5205 20
7654 MARTIN SALESMAN 7698 28-SEP-81 20013 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2865 30
7782 CLARK MANAGER 7839 09-JUN-81 2487 10
7788 SCOTT ANALYST 7566 19-APR-87 3026 20
7839 KING PRESIDENT 17-NOV-81 5034 10
7844 TURNER SALESMAN 7698 08-SEP-81 1524 0 30
7876 ADAMS CLERK 7788 23-MAY-87 11000 20
7900 JAMES CLERK 7698 03-DEC-81 12000 30
7902 FORD ANALYST 7566 03-DEC-81 3029 20
7934 MILLER CLERK 7782 23-JAN-82 13000 10
14 rows selected.
Session 2 :-
SQL> SET TRANSACTION READ ONLY;
Transaction set.
SQL> SELECT * FROM emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 5 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1617 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1275 500 30
7566 JONES MANAGER 7839 02-APR-81 5205 20
7654 MARTIN SALESMAN 7698 28-SEP-81 20013 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2865 30
7782 CLARK MANAGER 7839 09-JUN-81 2487 10
7788 SCOTT ANALYST 7566 19-APR-87 3026 20
7839 KING PRESIDENT 17-NOV-81 5034 10
7844 TURNER SALESMAN 7698 08-SEP-81 1524 0 30
7876 ADAMS CLERK 7788 23-MAY-87 11000 20
7900 JAMES CLERK 7698 03-DEC-81 12000 30
7902 FORD ANALYST 7566 03-DEC-81 3029 20
7934 MILLER CLERK 7782 23-JAN-82 13000 10
14 rows selected.
Session 1 :-
SQL> UPDATE emp SET sal = 10 WHERE ename = 'SMITH';
1 row updated.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 10 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1617 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1275 500 30
7566 JONES MANAGER 7839 02-APR-81 5205 20
7654 MARTIN SALESMAN 7698 28-SEP-81 20013 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2865 30
7782 CLARK MANAGER 7839 09-JUN-81 2487 10
7788 SCOTT ANALYST 7566 19-APR-87 3026 20
7839 KING PRESIDENT 17-NOV-81 5034 10
7844 TURNER SALESMAN 7698 08-SEP-81 1524 0 30
7876 ADAMS CLERK 7788 23-MAY-87 11000 20
7900 JAMES CLERK 7698 03-DEC-81 12000 30
7902 FORD ANALYST 7566 03-DEC-81 3029 20
7934 MILLER CLERK 7782 23-JAN-82 13000 10
14 rows selected.
Session 2 :-
SQL> SELECT * FROM emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 5 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1617 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1275 500 30
7566 JONES MANAGER 7839 02-APR-81 5205 20
7654 MARTIN SALESMAN 7698 28-SEP-81 20013 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2865 30
7782 CLARK MANAGER 7839 09-JUN-81 2487 10
7788 SCOTT ANALYST 7566 19-APR-87 3026 20
7839 KING PRESIDENT 17-NOV-81 5034 10
7844 TURNER SALESMAN 7698 08-SEP-81 1524 0 30
7876 ADAMS CLERK 7788 23-MAY-87 11000 20
7900 JAMES CLERK 7698 03-DEC-81 12000 30
7902 FORD ANALYST 7566 03-DEC-81 3029 20
7934 MILLER CLERK 7782 23-JAN-82 13000 10
14 rows selected.SET TRANSACTION READ WRITE
This version makes the current transaction as read-write and is the default setting.
Session 1 :-
SQL> SELECT * FROM emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 10 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1617 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1275 500 30
7566 JONES MANAGER 7839 02-APR-81 5205 20
7654 MARTIN SALESMAN 7698 28-SEP-81 20013 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2865 30
7782 CLARK MANAGER 7839 09-JUN-81 2487 10
7788 SCOTT ANALYST 7566 19-APR-87 3026 20
7839 KING PRESIDENT 17-NOV-81 5034 10
7844 TURNER SALESMAN 7698 08-SEP-81 1524 0 30
7876 ADAMS CLERK 7788 23-MAY-87 11000 20
7900 JAMES CLERK 7698 03-DEC-81 12000 30
7902 FORD ANALYST 7566 03-DEC-81 3029 20
7934 MILLER CLERK 7782 23-JAN-82 13000 10
14 rows selected.
SQL> UPDATE emp SET sal = 15 WHERE ename = 'SMITH';
1 row updated.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 15 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1617 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1275 500 30
7566 JONES MANAGER 7839 02-APR-81 5205 20
7654 MARTIN SALESMAN 7698 28-SEP-81 20013 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2865 30
7782 CLARK MANAGER 7839 09-JUN-81 2487 10
7788 SCOTT ANALYST 7566 19-APR-87 3026 20
7839 KING PRESIDENT 17-NOV-81 5034 10
7844 TURNER SALESMAN 7698 08-SEP-81 1524 0 30
7876 ADAMS CLERK 7788 23-MAY-87 11000 20
7900 JAMES CLERK 7698 03-DEC-81 12000 30
7902 FORD ANALYST 7566 03-DEC-81 3029 20
7934 MILLER CLERK 7782 23-JAN-82 13000 10
14 rows selected.
Session 2 :-
SQL> SET TRANSACTION READ WRITE;
Transaction set.
SQL> SELECT * FROM emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 15 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1617 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1275 500 30
7566 JONES MANAGER 7839 02-APR-81 5205 20
7654 MARTIN SALESMAN 7698 28-SEP-81 20013 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2865 30
7782 CLARK MANAGER 7839 09-JUN-81 2487 10
7788 SCOTT ANALYST 7566 19-APR-87 3026 20
7839 KING PRESIDENT 17-NOV-81 5034 10
7844 TURNER SALESMAN 7698 08-SEP-81 1524 0 30
7876 ADAMS CLERK 7788 23-MAY-87 11000 20
7900 JAMES CLERK 7698 03-DEC-81 12000 30
7902 FORD ANALYST 7566 03-DEC-81 3029 20
7934 MILLER CLERK 7782 23-JAN-82 13000 10
14 rows selected.
Session 1 :-
SQL> UPDATE emp SET sal = 20 WHERE ename = 'SMITH';
1 row updated.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 20 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1617 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1275 500 30
7566 JONES MANAGER 7839 02-APR-81 5205 20
7654 MARTIN SALESMAN 7698 28-SEP-81 20013 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2865 30
7782 CLARK MANAGER 7839 09-JUN-81 2487 10
7788 SCOTT ANALYST 7566 19-APR-87 3026 20
7839 KING PRESIDENT 17-NOV-81 5034 10
7844 TURNER SALESMAN 7698 08-SEP-81 1524 0 30
7876 ADAMS CLERK 7788 23-MAY-87 11000 20
7900 JAMES CLERK 7698 03-DEC-81 12000 30
7902 FORD ANALYST 7566 03-DEC-81 3029 20
7934 MILLER CLERK 7782 23-JAN-82 13000 10
14 rows selected.
Session 2 :-
SQL> SELECT * FROM emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 20 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1617 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1275 500 30
7566 JONES MANAGER 7839 02-APR-81 5205 20
7654 MARTIN SALESMAN 7698 28-SEP-81 20013 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2865 30
7782 CLARK MANAGER 7839 09-JUN-81 2487 10
7788 SCOTT ANALYST 7566 19-APR-87 3026 20
7839 KING PRESIDENT 17-NOV-81 5034 10
7844 TURNER SALESMAN 7698 08-SEP-81 1524 0 30
7876 ADAMS CLERK 7788 23-MAY-87 11000 20
7900 JAMES CLERK 7698 03-DEC-81 12000 30
7902 FORD ANALYST 7566 03-DEC-81 3029 20
7934 MILLER CLERK 7782 23-JAN-82 13000 10
14 rows selected.SET TRANSACTION ISOLATION LEVEL SERIALIZABLE | READ COMMITTED
This version defines how transactions that modify the database should be handled.
• When you specify SERIALIZABLE, a DML statement that attempts to modify a table already modified in an uncommitted transaction will fail.
• If you specify READ COMMITTED, a DML statement that attempts to modify a table already modified in an uncommitted transaction will wait until those row locks are released. This is the default.
Session 1 :-
SQL> UPDATE emp SET sal = 25 WHERE ename = 'SMITH';
1 row updated.
--No COMMIT Has Been Issued.
SQL> SELECT * FROM emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 25 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1617 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1275 500 30
7566 JONES MANAGER 7839 02-APR-81 5205 20
7654 MARTIN SALESMAN 7698 28-SEP-81 20013 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2865 30
7782 CLARK MANAGER 7839 09-JUN-81 2487 10
7788 SCOTT ANALYST 7566 19-APR-87 3026 20
7839 KING PRESIDENT 17-NOV-81 5034 10
7844 TURNER SALESMAN 7698 08-SEP-81 1524 0 30
7876 ADAMS CLERK 7788 23-MAY-87 11000 20
7900 JAMES CLERK 7698 03-DEC-81 12000 30
7902 FORD ANALYST 7566 03-DEC-81 3029 20
7934 MILLER CLERK 7782 23-JAN-82 13000 10
14 rows selected.
Session 2 :-
SQL> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Transaction set.
SQL> UPDATE emp SET sal = 30 WHERE ename = 'SMITH';
<<<HANGS>>>
Session 1 :-
SQL> COMMIT;
Commit complete.
Session 2 :-
SQL> UPDATE emp SET sal = 30 WHERE ename = 'SMITH';
UPDATE EMP SET SAL = 30 WHERE ENAME = 'SMITH'
*
ERROR at line 1:
ORA-08177: can't serialize access for this transaction
Session 1 :-
SQL> UPDATE emp SET sal = 45 WHERE ename = 'SMITH';
1 row updated.
--No COMMIT Has Been Issued.
SQL> SELECT * FROM emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 45 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1617 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1275 500 30
7566 JONES MANAGER 7839 02-APR-81 5205 20
7654 MARTIN SALESMAN 7698 28-SEP-81 20013 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2865 30
7782 CLARK MANAGER 7839 09-JUN-81 2487 10
7788 SCOTT ANALYST 7566 19-APR-87 3026 20
7839 KING PRESIDENT 17-NOV-81 5034 10
7844 TURNER SALESMAN 7698 08-SEP-81 1524 0 30
7876 ADAMS CLERK 7788 23-MAY-87 11000 20
7900 JAMES CLERK 7698 03-DEC-81 12000 30
7902 FORD ANALYST 7566 03-DEC-81 3029 20
7934 MILLER CLERK 7782 23-JAN-82 13000 10
14 rows selected.
Session 2 :-
SQL> SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Transaction set.
SQL> UPDATE emp SET sal = 50 WHERE ename = 'SMITH';
<<<HANGS>>>
Session 1 :-
SQL> COMMIT;
Commit complete.
Session 2 :-
SQL> UPDATE emp SET sal = 50 WHERE ename = 'SMITH';
1 row updated.SET TRANSACTION USE ROLLBACK SEGMENT
This version assigns the current transaction to the specified rollback segment and establishes the transaction as READ WRITE.
This statement cannot be used with SET TRANSACTION READ ONLY.
A Rollback Segment is a database object containing before-image of data written to the database.
Rollback segments are used to :-
Undo changes when a transaction is rolled back.
Recover the database to a consistent state in case of failures.
SQL> CONN / AS SYSDBA
Connected.
SQL> SELECT banner FROM v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
SQL> SELECT segment_name,tablespace_name FROM dba_rollback_segs;
SEGMENT_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SYSTEM SYSTEM
_SYSSMU1_3741171459$ UNDOTBS1
_SYSSMU2_1557575246$ UNDOTBS1
_SYSSMU3_3193270435$ UNDOTBS1
_SYSSMU4_2372004640$ UNDOTBS1
_SYSSMU5_553218552$ UNDOTBS1
_SYSSMU6_3809612316$ UNDOTBS1
_SYSSMU7_3306078869$ UNDOTBS1
_SYSSMU8_115127830$ UNDOTBS1
_SYSSMU9_4165470211$ UNDOTBS1
_SYSSMU10_3563521556$ UNDOTBS1
_SYSSMU11_3104252932$ UNDOTBS1
12 rows selected.
SQL> CREATE ROLLBACK SEGMENT rs1 TABLESPACE UNDOTBS1 STORAGE (INITIAL 20K NEXT 40K MINEXTENTS 2 MAXEXTENTS 50);
Rollback segment created.
SQL> SET TRANSACTION USE ROLLBACK SEGMENT rs1;
Transaction set.
Great reading.
Thank you Sir.
Very good
nice one
Nice work!
Great to see another voice sharing experiences and wisdom about Oracle Database. Thanks for taking the time to do this, Shoumadip!
Access paths are ways in which data is retrieved from the database. In general, index access paths should be used for statements that retrieve a small subset of table rows, while full scans are more efficient when accessing a large portion of the table. Online transaction processing (OLTP) applications, which consist of short-running SQL statements with high selectivity, often are characterized by the use of index access paths. Decision support systems, on the other hand, tend to use partitioned tables and perform full scans of the relevant partitions.
It’s really a nice and helpful piece of info. I’m glad that you simply shared this helpful info with us.
Please keep us informed like this. Thank you for sharing.