Friday, July 27, 2007

Flashback drop a table

Today I have change to use flashback drop table. A developer came to me and tell me what happend in one 10g databse.

With the help of "flashback table abc to before drop", I recovered it within minutes.
Compare to doing the same thing in 9i,
1. use lominer to find the time when the table was dropped.
2. Restored the database in anothe place
3. Perform incomplete revoery to the timestamp using backup controlfile
4. Export the table and import to production.

Guess how many time is needed.

More to add
flashback doesn't use undo tablespace.
You can recover a dropped table using Oracle’s recycle bin functionality: a dropped table is
stored in a special structure in the tablespace and is available for retrieval as long as the space
occupied by the table in the tablespace is not needed for new objects. Even if the table is no
longer in the tablespace’s recycle bin, depending on the criticality of the dropped table, you can
use either tablespace point in time recovery (TSPITR) or Flashback Database Recovery to
recover the table, taking into consideration the potential data loss for other objects stored in the
same tablespace for TSPITR or in the database if you use Flashback Database Recovery.