ORA-01456 (SET TRANSACTION READ ONLY - Part 3)

This was tested on an Oracle 11.2 database. If you try to INSERT, UPDATE or DELETE rows in a table during a READ ONLY transaction, you get an ORA-01456:

 

SQL> create table tab1 (col1 number)

  2  /

 

Table created.

 

SQL> insert into tab1 values(1)

  2  /

 

1 row created.

 

SQL> commit

  2  /

 

Commit complete.

 

SQL> set transaction read only

  2  /

 

Transaction set.

 

SQL> update tab1 set col1 = 2

  2  /

update tab1 set col1 = 2

       *

ERROR at line 1:

ORA-01456: may not perform insert/delete/update

operation inside a READ ONLY transaction

 

SQL>

 

If you do a COMMIT, this terminates the READ ONLY transaction:

 

SQL> commit

  2  /

 

Commit complete.

 

SQL>

 

… and the UPDATE statement works OK:

 

SQL> update tab1 set col1 = 2

  2  /

 

1 row updated.

 

SQL> commit

  2  /

 

Commit complete.

 

SQL>

 

The example below is similar to the one above. However, this time a ROLLBACK is used to terminate the READ ONLY transaction:

 

SQL> set transaction read only

  2  /

 

Transaction set.

 

SQL> update tab1 set col1 = 3

  2  /

update tab1 set col1 = 3

       *

ERROR at line 1:

ORA-01456: may not perform insert/delete/update

operation inside a READ ONLY transaction

 

SQL> rollback

  2  /

 

Rollback complete.

 

SQL> update tab1 set col1 = 3

  2  /

 

1 row updated.

 

SQL> drop table tab1

  2  /

 

Table dropped.

 

SQL>