« Oracle Certified SQL Expert
oracle 11g hits the streets »

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

I re-edited this post and it is unresolved yet. I thought it was related to system time, but apparently not :o 


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

:twisted:

This entry was posted on Tuesday, July 10th, 2007 at 14:32 and is filed under oracle, sql, dba. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.
Post to : del.icio.us - Digg it - Furl - reddit - Spurl

Put your code in <code> and </code> tags

13 Responses to “ORA-01466: unable to read data - table definition has changed”

  1. Sokrates Says:
    July 11th, 2007 at 12:38

    does not reproduce for me.

    Can you please provide Server OS & Version, Client OS ?

  2. Laurent Schneider Says:
    July 11th, 2007 at 14:08

    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

  3. Laurent Schneider Says:
    July 11th, 2007 at 16:03

    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 :? :

  4. Laurent Schneider Says:
    July 11th, 2007 at 16:36

    I edited the post because I can reproduce this locally on the database server, so it cannot be the systime only :(

  5. Laurent Schneider Says:
    July 11th, 2007 at 16:40

    it is perhaps due to the non-accuracy of translation of the last ddl time to a scn ;)

  6. Bob Carlton Says:
    July 13th, 2007 at 18:39

    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>

  7. Laurent Schneider Says:
    July 13th, 2007 at 19:50

    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]”

  8. Bob Carlton Says:
    July 17th, 2007 at 07:11

    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>

  9. Asif Momen Says:
    July 25th, 2007 at 09:02

    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.

  10. Laurent Schneider Says:
    July 25th, 2007 at 10:29

    interesting! thanks for the test case

  11. Laurent Schneider Says:
    July 26th, 2007 at 17:53

    what is your user? If you use SYS, it is not reproducible, because set transaction read only does not work for SYS

  12. Laurent Schneider Says:
    July 26th, 2007 at 18:14

    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

  13. Asif Momen Says:
    August 12th, 2007 at 11:30

    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

Leave a Reply

Use <code> and </code> to post code