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>