Hi and thank to all in advance!

I´m new in this forum, so ...

I need to know how to recover a table which has change in the table definition. I explain my situation with an example:

1.- I drop a colum in the table

alter table test1 drop column c;

2.- I need to comeback in the time to recovery the before state, but not with a import.

In first time i thought to set a recovery point with:

CREATE RESTORE POINT uno;

And then (after the alter table) recovery the table to that point with:

FLASHBACK TABLE test1 TO RESTORE POINT uno;

but.....

ERROR at line 1:
ORA-01466: unable to read data - table definition has changed



Someone could to say me how to solve thar problem?

thousand Thanks!

Do you not have an export of the table? Flashback table doesn't work if the definition has changed. You can flashback database or restore and recover.

thanks!

The problem is that the table is very "big" and the export is very slow to do,moreover I will to do it with more tables like that, so I ask other option...

when you say restore and recover you are referring to restore from simple backup?

>> the table is very "big" and the export is very slow to do,moreover I will to do it with more tables like that, so I ask other option...

So how about an RMAN backup? You can backup a gig per minute on a fast server.

Whati s it, exactly, that you are try to accomplish?

thanks Donald!

The way you say is the same that I was going to do but I wanted to investigate if there was other way to do this, like using FLASHBACK or another.

Does RMAN allow to restore only one table (Remember: the table isn´t dropped)? How?



Thanks for all your help to all!

"Does RMAN allow to restore only one table (Remember: the table isn´t dropped)? How?"

Just restore to a new instance and then copy the table over.

thanks aussie_dba!

I supoused that, but...

I´m thinking there is a little black hole in the recover, because, I think that recovering a table which was altered in the definition it´s a comun situation and there would be another methods to recover it, wouldn´t it?

thanks!

This is no different than recovering from user error. User performs DML and commits. Oops, didn't mean to do that. How do you recover the data (outside of flashback table since DDL changed)?

Like I said before, there is a "black hole" about this theme, because I think there should be a recovery system that would allow recover a table easily to a previous state without need for the table is dropped.

...but it is only a thought myself...

Pick a situation and stick with it. You've been given more than one answer in each case. What black hole are you talking about? There are specific option for specific scenarios. What is your question at this point?

How could I restore a table(the table has undergone changes definition) in a point of time?

Once again:
import from an export taken prior
RMAN or other hot backup - tablespace point in time recovery
flashback database

This is basic stuff, covered well in the documentation.

erased by misunderstanding

Sorry if you feel belittled when being told the nature of your problem is basic and covered in the documentation. Would you feel better if I had cut and pasted 20 pages of a pdf doc here?

erased by misunderstanding...

>> I thought this is a forum to resolve questions

You asked, and Steve answered, giveing you several options:



What's the problem, he answered correctly.

There is a difference between answering questions, and spoon-feeding people who, for whatever reason, are unwilling to read about the docs. Like Steve said, it would be silly to paste-in 30 pages from the documentation.

************************************************************************

>> people like you is what makes me think back to ask my problems in it

FYI, Steve Callan is one of the most respected Oracle authors in the world, and you are VERY LUCKY to get his assistance.

http://www.databasejournal.com/article.php/3099361

************************************************************************
>> How could I restore a table(the table has undergone changes definition) in a point of time?

Have you considered tracking changes with DDL triggers?

http://www.dba-oracle.com/t_ddl_triggers.htm

I would like to apologise publicly to Esteve for having misunderstood the words and and thank him for his invaluable assistance.

I also believe, as Donald says, that Steve is one of the best authors of Oracle's world.

Thanks and ... sorry Steve for my "misunderstood"!

>> Steve is one of the best authors of Oracle's world.

Oh yes, Steve is one of my favorites!

I'm really looking forward to his new data warehousing book:

http://www.rampant-books.com/book_2009_data_warehousing.htm

I don´t know it, and the truth is that it seems a good book,doesn´t it?.