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