I re-edited this post and it is unresolved yet. I thought it was related to system time, but apparently not 😮
SQL> create table t(x number);
Table created.
SQL> set transaction read only ;
Transaction set.
SQL> select * from t;
select * from t
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed
If I wait one minute after my create table statement, it works
SQL> drop table t;
Table dropped.
SQL> create table t(x number);
Table created.
SQL> host sleep 60
SQL> set transaction read only;
Transaction set.
SQL> select * from t;
no rows selected
😈
does not reproduce for me.
Can you please provide Server OS & Version, Client OS ?
you need to have the time of your client (where you run sqlplus) set to a later value than the time of your database server.
I used WinXP + 10.2.0.1 as client
and
Solaris10 + 10.2.0.2 as server
Regards
Laurent
I could also reproduce it with
SQL> create table t (x number) enable row movement;
SQL> insert into t values (1);
SQL> commit;
SQL> select * from t;
X
----------
1
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
10017910
SQL> insert into t values (2);
SQL> commit;
SQL> select * from t;
X
----------
1
2
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
10017913
SQL> insert into t values (3);
SQL> commit;
SQL> select * from t;
X
----------
1
2
3
SQL> flashback table t to scn 10017910;
flashback table t to scn 10017910
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed
this is strange, and probably difficult to reproduce, but it is documented on Metalink that the system time should be accurate to avoid that kind of error. Still it can be something unrelated to time ❓
I edited the post because I can reproduce this locally on the database server, so it cannot be the systime only 🙁
it is perhaps due to the non-accuracy of translation of the last ddl time to a scn 😉
Are you running this from a script? i.e @myfile.sql
I get the same error when run via a sqlplus script.
When I key each command in one by one at the prompt no error.
I believe it’s the SQL cache.
When keying (in my normal slow manner. ha ha.) it doesn’t seem to matter if my client time is different then the server time.
using sqlplus script ———-
SQL> @test
Table dropped.
Table created.
Transaction set.
select * from t
*
ERROR at line 1:
ORA-01466: unable to read data – table definition has changed
hand keyed——
SQL> drop table t;
Table dropped.
SQL> create table t( x number);
Table created.
SQL> set transaction read only;
Transaction set.
SQL> select * from t;
no rows selected
SQL>
what about copy pasting all command in one step? I think it is due to the time you need to type “set transaction readonly [enter]”
Yes, copy/paste also generates the error. I tried it with host sleep 15, 5 and even 1 second and they were all ok. ( see commented sections below).
— copy/pasted and got an error —
SQL> drop table t
2 /
Table dropped.
SQL>
SQL> create table t( x number )
2 /
Table created.
SQL>
SQL> set transaction read only;
Transaction set.
SQL>
SQL> select * from t;
select * from t
*
ERROR at line 1:
ORA-01466: unable to read data – table definition has changed
SQL>
——copy/pasted w sleep 15 seconds, ok —-
SQL> drop table t
2 /
Table dropped.
SQL>
SQL> create table t( x number )
2 /
Table created.
SQL>
SQL> host sleep 15
SQL>
SQL> set transaction read only;
Transaction set.
SQL>
SQL> select * from t;
no rows selected
——copy/pasted w sleep 5 seconds, ok —-
SQL> drop table t
2 /
Table dropped.
SQL>
SQL> create table t( x number )
2 /
Table created.
SQL>
SQL> host sleep 5
SQL>
SQL> set transaction read only;
Transaction set.
SQL>
SQL> select * from t;
no rows selected
——copy/pasted w sleep 1 seconds! ok —-
SQL> drop table t
2 /
Table dropped.
SQL>
SQL> create table t( x number )
2 /
Table created.
SQL>
SQL> host sleep 1;
SQL>
SQL> set transaction read only;
Transaction set.
SQL>
SQL> select * from t;
no rows selected
SQL>
Hi Laurent,
My database server is Oracle 10g Release 2 on AIX box, and my client is on Windows. I reset the time of my client to be +2 hours and -2 hours of the server time and ran the test, but I did not face ORA-01466 error. I placed the create table, set transtaction, and the select in a script and ran it.
Following is the ouput of my run (in both cases where my client time is +2 and -2 hours from the database servers time).
SQL> @c
Table created.
Transaction set.
no rows selected
Following are the contents of my script “c.sql”
create table t(x number);
set transaction read only ;
select * from t;
I couldn’t reproduce the ORA-01466 error.
interesting! thanks for the test case
what is your user? If you use SYS, it is not reproducible, because set transaction read only does not work for SYS
I made a test on my notebook by running the script 1000 times on various versions
10.2.0.3 : reproduced 97.2%
9.2.0.8 : reproduced 96.9%
10.1.0.5 : reproduced 98.7%
11.1.0.5 beta : reproduced 94.8%
all versions affected, none consistently
Hi Laurent,
Rightly said. I was testing with user SYS. Below is a test as both SYS and non-SYS user.
SQL> conn test/test@mytestdb
Connected.
SQL>
SQL>
SQL> create table t(x number);
Table created.
SQL>
SQL> set transaction read only ;
Transaction set.
SQL>
SQL> select * from t;
select * from t
*
ERROR at line 1:
ORA-01466: unable to read data – table definition has changed
SQL> drop table t;
Table dropped.
SQL> conn sys/password@mytestdb as sysdba
Connected.
SQL> create table t(x number);
Table created.
SQL> set transaction read only ;
Transaction set.
SQL> select * from t;
no rows selected
i had this set transaction read only within a package.
When its sys, its ok, when its not sys, its a problem.
I had a similar kind of issue while running the jobs in DataStage
When changed Transaction Isolation form Read Only to Read Committed this issue got resolved.