ORA-01466 (SET TRANSACTION READ ONLY - Part 1)

Going through some SQL*Plus course notes from 1990 (as you do) I came across the SET TRANSACTION READ ONLY statement and decided to try it out in an Oracle 11.2 database. It didn’t quite work as expected:

 

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> select * from tab1

  2  /

select * from tab1

              *

ERROR at line 1:

ORA-01466: unable to read data - table definition has

changed

 

SQL> drop table tab1

  2  /

 

Table dropped.

 

SQL>

 

I found that if I waited a moment after the COMMIT statement, the problem disappeared:

 

SQL> create table tab2 (col1 number)

  2  /

 

Table created.

 

SQL> insert into tab2 values(1)

  2  /

 

1 row created.

 

SQL> commit

  2  /

 

Commit complete.

 

SQL> host sleep 1

 

SQL> set transaction read only

  2  /

 

Transaction set.

 

SQL> select * from tab2

  2  /

 

      COL1

----------

         1

 

SQL> drop table tab2

  2  /

 

Table dropped.

 

SQL>