Home » SQL & PL/SQL » SQL & PL/SQL » ora-01466
ora-01466 [message #257918] Thu, 09 August 2007 10:39 Go to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Hi,

i gone through http://www.orafaq.com/forum/t/84481/99054/ post.

I did the following step.

1)create table yash_test(name varchar2(30))

2)insert into yash_test values('Yash');
  insert into yash_test values('Shan');
commit;
3)update  yash_test set name='Vikranth';
commit;
4)SELECT name FROM yash_test AS OF TIMESTAMP  (SYSTIMESTAMP - INTERVAL '1' DAY)


Step four i am getting following error.
ORA-01466: unable to read data - table definition has changed

Please suggest me. Why i am getting this error.

--Yash
icon14.gif  Re: ora-01466 [message #257919 is a reply to message #257918] Thu, 09 August 2007 10:50 Go to previous messageGo to next message
sanka_yanka
Messages: 184
Registered: October 2005
Location: Kolkata
Senior Member

It will shows this error because you are putting the interval time one day before the table created. It will take the time difference after the table created i.e. if you have created the table 5 min before then you can run the query something like
SELECT name FROM yash_test AS OF TIMESTAMP  (SYSTIMESTAMP - interval '5' MINUTE)

It will shows the data but if you try with the below query
SELECT name FROM yash_test AS OF TIMESTAMP  (SYSTIMESTAMP - interval '1' DAY)

then it will throws this error.
I hope you understand.

Cheers
Sanka
Re: ora-01466 [message #257920 is a reply to message #257918] Thu, 09 August 2007 10:50 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
You are getting the error because the table definition has changed. I think Oracle has put it quite clear. If you have done any alterations on the table definition during the last 24 hours (sysdate -1) and now, you get that error. Oracle is unable to retrieve the data. Bummer, no flashback I'm afraid.

But it might be possible that I'm wrong. I didn't check this. Perhaps someone else knows a way out.

MHE
Re: ora-01466 [message #257924 is a reply to message #257920] Thu, 09 August 2007 11:03 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Thx Sanka!!

Maaher sanka is right I have just now table created. i was given interval time before the table created.


--Yash
Re: ora-01466 [message #257937 is a reply to message #257924] Thu, 09 August 2007 11:35 Go to previous messageGo to next message
Habeeb
Messages: 57
Registered: August 2000
Member
I just created a table;

SQL> SELECT oper_id FROM m1 AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY);
SELECT oper_id FROM m1 AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY)
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed

No changes to the table were made.

SQL> SELECT oper_id FROM m1 AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' minute);

OPER_ID
----------
1
2
3

Habeeb
Re: ora-01466 [message #257948 is a reply to message #257937] Thu, 09 August 2007 12:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I just created a table;

Quote:
No changes to the table were made.

What can be a bigger change than you didn't exist and now you do?

Regards
Michel

[Updated on: Thu, 09 August 2007 12:08]

Report message to a moderator

Re: ora-01466 [message #257952 is a reply to message #257948] Thu, 09 August 2007 12:14 Go to previous messageGo to next message
Habeeb
Messages: 57
Registered: August 2000
Member
Very funny, am I supposed to laugh?
I though this is an IT support forum, I am sorry but I am finding more sarcasms and rudeness on this forum rather then providing help.

Habeeb
Re: ora-01466 [message #257953 is a reply to message #257918] Thu, 09 August 2007 12:16 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
What don't you understand ???

You are trying to query on a table as of a timestamp when the table didn't exist.

If you were writing the oracle executable layer how would you handle this situation other than throw an error?
Re: ora-01466 [message #257957 is a reply to message #257952] Thu, 09 August 2007 12:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No intention either offend or be funny just a light way to say that you can't query a table before it exists.
Make sense, doesn't it?

Keep it easy or you will suffer on forum, of any kind.

Regards
Michel
Re: ora-01466 [message #257962 is a reply to message #257957] Thu, 09 August 2007 12:29 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Ah! I thought of this on the way home. Good one Sanka! I should have put it more general: If any DDL concerning the table has taken place between now and the flashback time you want to query... I warned you I didn't check it. Very Happy

MHE
Previous Topic: Splitting one column inti two columns
Next Topic: Outer Join Constructs
Goto Forum:
  


Current Time: Tue Dec 06 08:44:34 CST 2016

Total time taken to generate the page: 0.07929 seconds