Understanding SET TRANSACTION In Oracle 19c

RealTime Mview

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.

Shoumadip Das

Hi Folks, I am Shoumadip Das hailing from Kolkata, India welcoming you to explore my blog www.oraclemasterpiece.com. I am a self motivated and successful professional working in a leading IT Giant for more than 10 years.My area of expertise includes –

  • Oracle Database Programming (SQL & PL/SQL)
  • Perl Programming
  • Unix Programming

Read More

8 thoughts on “Understanding UNUSABLE Index”

  1. Great to see another voice sharing experiences and wisdom about Oracle Database. Thanks for taking the time to do this, Shoumadip!

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

Leave a Reply

Your email address will not be published. Required fields are marked *