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

I am trying to query between versions for flashback and am getting the following error:

SQL> SELECT versions_startscn, versions_starttime,
2 versions_endscn, versions_endtime,
3 versions_xid, versions_operation,
4 description
5 FROM flashback_version_query_test
6 VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('2004-07-06 11:29:48', 'YYYY-MM-DD HH24:MI:SS')
7 AND TO_TIMESTAMP('2004-07-06 11:30:32', 'YYYY-MM-DD HH24:MI:SS')
8 WHERE id = 1;
FROM flashback_version_query_test
*
ERROR at line 5:
ORA-01466: unable to read data - table definition has changed

The flashback is configured successfuly and the time I have put in is also correct.

Can someone tell me what could be the issue..

There might be tiny clue in the error message.

Quote:

---

Originally posted by JMac
There might be tiny clue in the error message.

---

What is it? The timestamps I have input are the ones basically before and after an update transaction (committed transaction). So when I query, it should give me data instead of the error, assuming the flashback recovery area is configured successfully. So...

Quote:

---

Originally posted by Vipassana
I am trying to query between versions for flashback and am getting the following error:

SQL> SELECT versions_startscn, versions_starttime,
2 versions_endscn, versions_endtime,
3 versions_xid, versions_operation,
4 description
5 FROM flashback_version_query_test
6 VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('2004-07-06 11:29:48', 'YYYY-MM-DD HH24:MI:SS')
7 AND TO_TIMESTAMP('2004-07-06 11:30:32', 'YYYY-MM-DD HH24:MI:SS')
8 WHERE id = 1;
FROM flashback_version_query_test
*
ERROR at line 5:
ORA-01466: unable to read data - table definition has changed

The flashback is configured successfuly and the time I have put in is also correct.

Can someone tell me what could be the issue..

---

It looks as though you are trying to view the data in too discrete of a time period. You can only hope to be accurate with dbms_flashback to +/- 5 minutes so even though you invoked flashback query at time X it may actually be time X +/- 5 minutes

Steve

Hi Vips

Flashback will not work if u perform DDL in between..not in any version of oracle..

if you want to test flashback..there should not be ddl performed on the the object in question

regards
Hrishy

Quote:

---

Originally posted by Vipassana
I am trying to query between versions for flashback and am getting the following error:

SQL> SELECT versions_startscn, versions_starttime,
2 versions_endscn, versions_endtime,
3 versions_xid, versions_operation,
4 description
5 FROM flashback_version_query_test
6 VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('2004-07-06 11:29:48', 'YYYY-MM-DD HH24:MI:SS')
7 AND TO_TIMESTAMP('2004-07-06 11:30:32', 'YYYY-MM-DD HH24:MI:SS')
8 WHERE id = 1;
FROM flashback_version_query_test
*
ERROR at line 5:
ORA-01466: unable to read data - table definition has changed

The flashback is configured successfuly and the time I have put in is also correct.

Can someone tell me what could be the issue..

---

Quote:

---

Originally posted by hrishy
Hi Vips

Flashback will not work if u perform DDL in between..not in any version of oracle..

if you want to test flashback..there should not be ddl performed on the the object in question

regards
Hrishy

---

yeah that was sort of my point

if you create table at 9:00 with some dummy data and then
at 9:01 you update the table and then
at 9:04 you invoke dbms_flasback for 9:00 then you might encounter the error condition that the user encountered as dbms_flashback is only accurate to within 5 minute increment from the time of the initial SCN at db startup