- 24 Dec, 2023
- 0 Comments
- 2 Mins Read
ORA-01466: Unable To Read Data
Solution : Generally , This error happens when a DDL change happened on that object and we are trying to flashback to a timestamp, before that DDL change.
Suppose , DDL change happened to the database at 5 PM, and if you are trying to flashback to 4 PM,( before the DDL change), then flashback will fail with ORA-01466 error.
So flashback is possible only to a timestamp after the DDL change.
Let’s see with Example :
1. Let’s truncate a table ( TRUNCATE is a DDL statement)
1 2 3 4 5 6 7 8 9 10 | 16:00:00 SQL> select count(*) from test1;COUNT(*)----------13593217:00:00 SQL> truncate table test1;Table truncated. |
Here I have truncated the table at 17:00 hrs. Now we will try to flashback to a timestamp before the DDL, say 16:30 hrs.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | SQL> FLASHBACK TABLE test1 TO TIMESTAMP TO_TIMESTAMP('2023-07-12 16:30:00', 'YYYY-MM-DD HH24:MI:SS');FLASHBACK TABLE test1 TO TIMESTAMP TO_TIMESTAMP('2023-07-12 16:30:00', 'YYYY-MM-DD HH24:MI:SS')*ERROR at line 1:ORA-08189: cannot flashback the table because row movement is not enabled16:00:00 SQL> select count(*) from test1;COUNT(*)----------13593217:00:00 SQL> truncate table test1;Table truncated. |
Here I have truncated the table at 17:00 hrs. Now we’ll try to flashback to a timestamp before the DDL, say 16:30 hrs.
1 2 3 4 5 | SQL> FLASHBACK TABLE test1 TO TIMESTAMP TO_TIMESTAMP('2023-07-12 16:30:00', 'YYYY-MM-DD HH24:MI:SS');FLASHBACK TABLE test1 TO TIMESTAMP TO_TIMESTAMP('2023-07-12 16:30:00', 'YYYY-MM-DD HH24:MI:SS')*ERROR at line 1:ORA-08189: cannot flashback the table because row movement is not enabled |
Here we got different error, because row movement was not enabled for the table. Let’s enable it.
1 2 3 4 5 6 7 8 9 10 | SQL> alter table test1 enable row movement;Table altered.SQL> FLASHBACK TABLE test1 TO TIMESTAMP TO_TIMESTAMP('2023-07-12 16:30:00', 'YYYY-MM-DD HH24:MI:SS');FLASHBACK TABLE test1 TO TIMESTAMP TO_TIMESTAMP('2023-07-12 16:30:00', 'YYYY-MM-DD HH24:MI:SS')*ERROR at line 1:ORA-01466: unable to read data - table definition has changed |