Re: Recover deleted rows from a table from archive logs?

sybrandb wrote:
> Same with respect to flashback. Undo segments are ordinary segments,
> and are written to the redolog!

That Steven is correct here (or at least I suppose it demonstrates that Oracle doesn't use the logs for it) can be demonstrated quite simply since a database not in archivelog mode can still recover data that isn't in any current logfile. Having to set UNDO_RETENTION would be a rather large clue as well.

SQL> select current_scn,log_mode

   2 from v$database;

CURRENT_SCN LOG_MODE

• ------------

      839554 NOARCHIVELOG SQL> delete t1;

49744 rows deleted.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select group#,first_change#

   2 from v$log;

     GROUP# FIRST_CHANGE#

• -------------

          1 840313

          2 840317

          3 840323

SQL> select count(*)

   2 from t1
   3 as of scn 839554;

   COUNT(*)

      49744

SQL> spool off

regards

-- 

Niall Litchfield
Oracle DBA
http://www.orawin.info/services/