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

Where I currently work a significant proportion of business is done on-line, via the web or mobile app. For the OLTP side of the systems this means we have a strong focus on performance and availability. As an example, we deliberately avoid package state in our database packages so we can deploy new versions of code without having to take the system down. Similarly, many database maintenance activities are performed on-line too… which is why I was concerned when I arrived at work one morning to find an on-line session had encountered the following exception:

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

The operation in question was a simple SELECT against a single interval partitioned table. Cross referencing the application logs to the database activity the error occurred at the same time that a partition was dropped on the table. The partition drop was part of a standard job to remove old data. The DDL issued against the table was:

ALTER TABLE partition_test SET INTERVAL (INTERVAL '7' DAY)
ALTER TABLE partition_test DROP PARTITION earliest_partition

The first command shifts the last range partition to the last partition of the table, converting the interval partitions to range partitions, thereby avoiding the exception thrown if an attempt is made to drop the last range partition. This then allows the dropping of old partitions without trouble.

Testing using the packaged routines in separate sessions failed to generate ORA-01466. No matter the order that the SELECT or DDL was executed no problems were encountered. Reading up on ORA-01466 on-line, one scenario that can give rise to the error is the use of read only queries, which led to how the error occurred…

Let’s use a simple interval partitioned table populated with 50 rows to illustrate the problem:

CREATE TABLE partition_test
   (id      NUMBER(5))
PARTITION BY RANGE (id) INTERVAL (10)
(PARTITION partition_test_p1 VALUES LESS THAN (10))
/

INSERT INTO partition_test
SELECT ROWNUM
FROM   dual
CONNECT BY ROWNUM <= 50
/

COMMIT
/

If we execute our partition maintenance commands followed immediately by a simple SELECT then we get a correct result:

SQL> ALTER TABLE partition_test SET INTERVAL (10)
  2  /

Table altered.

SQL> ALTER TABLE partition_test DROP PARTITION partition_test_p1
  2  /

Table altered.

SQL> SELECT *
  2  FROM   partition_test
  3  WHERE  id = 40
  4  /

        ID
----------
        40

However if our SELECT is done within a session that is read only:

SQL> ALTER TABLE partition_test SET INTERVAL (10)
  2  /

Table altered.

SQL> ALTER TABLE partition_test DROP PARTITION partition_test_p1
  2  /

Table altered.

SQL> SET TRANSACTION READ ONLY
  2  /

Transaction set.

SQL> SELECT *
  2  FROM   partition_test
  3  WHERE  id = 40
  4  /
FROM   partition_test
       *
ERROR at line 2:
ORA-01466: unable to read data - table definition has changed

From Oracle Support information, if a read only query if issued immediately against a table that has had DDL done on it then it will result in ORA-01466.

So, going back to the original system error, the developer of the client application confirmed that the session was put into read-only mode as it had no requirement to modify data. Sensible thinking on behalf of the developer but unfortunately it resulted in the problem. The solution was to simply run the session in standard read/write mode.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s