Wednesday, April 03, 2013

ORA-01453 (SET TRANSACTION READ ONLY - Part 2)

Continuing with my investigation of SET TRANSACTION READ ONLY on an Oracle 11.2 database, I noticed that it fails with an ORA-01453 if you try to run it from a session with pending transactions. Once you have used COMMIT (or ROLLBACK) to deal with the pending transactions, the statement works OK:
 
SQL> create table tab1 (col1 number)
  2  /
 
Table created.
 
SQL> insert into tab1 values(1)
  2  /
 
1 row created.
 
SQL> set transaction read only
  2  /
set transaction read only
*
ERROR at line 1:
ORA-01453: SET TRANSACTION must be first statement of
transaction
 
SQL> commit
  2  /
 
Commit complete.
 
SQL> host sleep 1
 
SQL> set transaction read only
  2  /
 
Transaction set.
 
SQL> select * from tab1
  2  /
 
      COL1
----------
         1
 
SQL> drop table tab1
  2  /
 
Table dropped.
 
SQL>

No comments: