Thursday, April 04, 2013

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>

No comments: