- 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)
16:00:00 SQL> select count(*) from test1; COUNT(*) ---------- 135932 17: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.
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
16:00:00 SQL> select count(*) from test1;
COUNT(*)
----------
135932
17: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.
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.
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