This discussion is archived
9 Replies Latest reply: Jan 5, 2013 12:36 PM by 792482 RSS

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

792482 Newbie
Currently Being Moderated
We are hitting this issue while bringing up the db related service from BRM Application server .

Please can you point out the exact issue & resolution to fix this database issue.

Any suggested workaround for this oracle 11g db issue?

Thanks,
Satish
  • 1. Re: ORA-01466: unable to read data - table definition has changed
    sb92075 Guru
    Currently Being Moderated
    user772862 wrote:
    We are hitting this issue while bringing up the db related service from BRM Application server .

    Please can you point out the exact issue & resolution to fix this database issue.

    Any suggested workaround for this oracle 11g db issue?

    Thanks,
    Satish
    my car won't go
    tell me how to make my car go


    How do I ask a question on the forums?
    SQL and PL/SQL FAQ
  • 2. Re: ORA-01466: unable to read data - table definition has changed
    EdStevens Guru
    Currently Being Moderated
    user772862 wrote:
    We are hitting this issue while bringing up the db related service from BRM Application server .

    Please can you point out the exact issue & resolution to fix this database issue.
    MY quick google search of the reported error message (ora-01466) turns up a lot of possibilities, but no "exact issue & resolution".

    >
    Any suggested workaround for this oracle 11g db issue?
    What leads related to your specifics did YOUR search turn up?

    >
    Thanks,
    Satish
    " this database issue" and "this oracle 11g db issue" may not be a database issue at all.

    As you check out the hits you get from google, look particularly for a blog by Laurent Schneider. ;-)

    Edited by: EdStevens on Jan 4, 2013 11:10 AM

    Edited by: EdStevens on Jan 4, 2013 11:11 AM
  • 3. Re: ORA-01466: unable to read data - table definition has changed
    Mark Malakanov (user11181920) Expert
    Currently Being Moderated
    We are hitting this issue while bringing up the db related service from BRM Application server .
    Most likely the issue is in BRM Application server.
    Try to look into its logs and figure out what query is failing.
    May be one process of this AS is querying the table while another issues "alter table " or "truncate table ". The former will fail.

    Though I agree, the issue is partly in Oracle, because it does not set any lock on table(s) during a query. So the table or its index can be altered (DDLed) during being queried and it will impact all running queries that use this table/index.
    Because of this, developers and DBAs should pay attention when they run maintenance DDLs like TRUNCATE, INDEX REBUILD ONLINE, SPLIT PARTITION etc. It may impact running queries, so should be done during quiet time.

    Edited by: Mark Malakanov (user11181920) on Jan 4, 2013 12:16 PM
    Adding to this, critical 24x7 applications should be programmed with special techniques, for example issuing LOCK TABLE [PARTITION] WITH ROW SHARE MODE before querying and ROLLBACK/COMMIT after it. Or it should process the ORA-01466 exception by re-running the failed query. But I doubt that such techniques are widely used by application programmers.
    Also it is difficult to use because such locks will be released if application issues ROLLBACK/COMMIT during fetching a cursor(querying).

    Edited by: Mark Malakanov (user11181920) on Jan 4, 2013 12:16 PM
  • 4. Re: ORA-01466: unable to read data - table definition has changed
    792482 Newbie
    Currently Being Moderated
    Mark,

    I dont feel like its a BRM Apps server issue since if we point our same BRM Apps to 10g db, it works fine.

    Also, the reported issue would be resolved automatically after certain amount of time (may be after 8 to 10 hrs). Then no issues are noted.

    We are noticing this issue from Jan 01, 2013 onwards only. We haven't modified anything in BRM Apps server as well as 11g db.

    We are not able to figure out why we are hitting this issue & how it resolves automatically after 8-10 hours duration.

    Thanks,
    Satish
  • 5. Re: ORA-01466: unable to read data - table definition has changed
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    Pl post exact OS and database versions. Where is this error shown ? Are all the servers in the same timezone ? If not, you may be running into a bug

    Bug 6598432 - ORA-1466 from read-only transaction when client / server in different timezones [ID 6598432.8]
    Bug 12948663 - ORA-1466 when a global temp table queried from a READ ONLY transaction [ID 12948663.8]

    HTH
    Srini
  • 6. Re: ORA-01466: unable to read data - table definition has changed
    792482 Newbie
    Currently Being Moderated
    Hi Srini,

    Please find the details below :

    OS Version :
    ========
    Linux 2.6.18-308.0.0.0.1.el5xen #1 SMP Sat Feb 25 16:26:29 EST 2012 x86_64 x86_64 x86_64 GNU/Linux

    DB Version :
    ========
    SQL*Plus: Release 11.1.0.6.0 - Production on Sat Jan 5 10:39:27 2013
    Copyright (c) 1982, 2007, Oracle. All rights reserved.

    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    SQL>

    Additional Info :
    ==========
    SQL> select sessiontimezone from dual;

    SESSIONTIMEZONE
    ---------------------------------------------------------------------------
    -08:00

    SQL> select dbtimezone from dual;

    DBTIME
    ------
    +00:00

    Kindly help us to resolve this issue.

    Thanks,
    Satish
  • 7. Re: ORA-01466: unable to read data - table definition has changed
    792482 Newbie
    Currently Being Moderated
    OS Date-Time-TZ : Sat Jan 5 10:41:51 PST 2013
    DB Date-Time-TZ : Sat Jan 5 10:42:57 PST 2013

    The below error is thrown in our BRM Apps server when we start the service from our BRM Apps server which internally connects to the database and error is thrown for SELECT query.

    ORACLE error: do_sql_mr_select: PINStmtExecute: code 1466, op 0
    =ORA-01466: unable to read data - table definition has changed


    Thanks,
    Satish
  • 8. Re: ORA-01466: unable to read data - table definition has changed
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    If the two MOS Docs I posted above do not help, then pl log an SR with Support for this issue.

    HTH
    Srini
  • 9. Re: ORA-01466: unable to read data - table definition has changed
    792482 Newbie
    Currently Being Moderated
    Thanks a lot Srini for the info.

    Looks like this issue is due to different timezones in oracle 11g client/server.

    This is a known issue in 11.1.0.6 DB version as per Oracle Support Document 6598432.8 (Bug 6598432 - ORA-1466 from read-only transaction when client / server in different timezones) which can be found at: https://support.oracle.com/epmos/faces/DocumentDisplay?id=6598432.8

    Issue Description :
    ===================
    SQL> select dbtimezone, sessiontimezone from dual;

    DBTIME
    ------
    SESSIONTIMEZONE
    ---------------------------------------------------------------------------
    +01:00
    -08:00

    Workaround :
    ============

    Step 1 : SQL> alter database set time_zone='-08:00'; Step 2 : Restart the database.
    Step 3 : SQL> select dbtimezone, sessiontimezone from dual;

    DBTIME
    ------
    SESSIONTIMEZONE
    ---------------------------------------------------------------------------
    -08:00
    -08:00

    I have verified the above workaround in our env and now all the services are coming up without any errors.

    Thanks,
    Satish

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points