Wednesday, April 03, 2013

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>
 
I looked up ORA-01466 on Google and found that Laurent Schneider, the author of the book advertised below, had the same problem six years ago so at least I am in good company.


No comments: