Re: Flashback table/drop: possible until... ?

Spendius wrote:
> Hi,
> Has someone experienced how long a table or a drop table
> can be flashbacked without problems once you've made a
> mistake ?
> Let's you have a table occupying 3 extents:
> 1/ You delete records in the 2nd extent: for how long
> will you be able to flashback them (and how does
> Oracle know that it can still recover them, is there
> some kind of rows markers for this block somewhere) ?

Oracle will attempt (prior to 10) to keep the undo records required for the interval specified by UNDO_RETENTION, if however you require UNDO for current transactions Oracle may overwrite records required for the flashback query. It is the retention of undo records that allows this functionality.

> 2/ You drop this table, but don't notice it immediately.
> Then Oracle needs one of these 3 ex-extents (say because
> the file's reached its max. size) for another segment:
> let's say Oracle chooses what was the 3rd extent; then
> you notice you mistakenly dropped this table: can you
> *partly* recover it or is it really too late for the
> whole segment, whatever the number of extent(s) still
> "available" (i.e. not yet occupied by a rival segment) ?

The Undrop facility essentially changes the drop table command to a rename table command (and some other stuff as well). The data won't get overwritten until either

1. the space reserved for deleted objects is full at which point new deletions obliterate old ones.
2. the tablespace is otherwise full and space is required by existing or new objects.

-- 
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com