ORA-01466
Tried to perform a flashback query, but it failed with ORA-01466.
SQL> select count(*) cnt from t1 as of timestamp to_timestamp('2023-02-19 22:15:00', 'yyyy-mm-dd hh24:mi:ss');
select count(*) cnt from t1 as of timestamp to_timestamp('2023-02-19 22:15:00', 'yyyy-mm-dd hh24:mi:ss');
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed
ORA-01466 means that the timestamp or SCN you specified in the statement is earlier than LAST_DDL_TIME, there's no way to read the data before then.
Solution
To conquer the problem, you have to use a time later than LAST_DDL_TIME. Let's check its LAST_DDL_TIME.
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> select last_ddl_time from all_objects where owner = 'HR' and object_type = 'TABLE' and object_name = 'T1';
LAST_DDL_TIME
-------------------
2023-02-19 22:17:59
OK, if we want to read any flashback data, we should set a time later than the time above.
SQL> select count(*) cnt from t1 as of timestamp to_timestamp('2023-02-19 22:18:30', 'yyyy-mm-dd hh24:mi:ss');
CNT
----------
107
If the TIMESTAMP or System Change Number (SCN) you specified in the original statement is really the one you want to use, then there's no way to trace back that far.
Table Definition
I know you haven't changed the table definition. As we have talked previously, the updating of LAST_DDL_TIME doesn't necessarily mean that the table definition has been changed. What DDL made LAST_DDL_TIME change could be:
- TRUNCATE TABLE
- GRANT or REVOKE
- CREAET INDEX ON
The above DDL won't change the table definition, so the error message may be somewhat misleading.
Preventive Action
To prevent any undo data from loss, the first rule is that don't use any DDL before retrieving the data back.
The second rule is that if you want to clear all data, please use DELETE instead of TRUNCATE before INSERT them back. This is because DELETE generates and keeps undo, TRUNCATE doesn't.