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:

14 thoughts on “ORA-01466: unable to read data – table definition has changed”

  1. 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

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

  3. 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>

  4. 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>

  5. 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.

  6. 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

  7. 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

  8. i had this set transaction read only within a package.

    When its sys, its ok, when its not sys, its a problem.

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>