Extract from Oracle at given time

Although I haven't tried it, I would tend to suspect that you'll run into problems when your UNDO_RETENTION exceeds the length of time that Oracle maintains its SCN to timestamp mapping. If memory serves, that is roughly 1 week (well, if memory serves, it was 1 week in the 10g days and I'm not aware of anything that would have changed that in 11.2). I would tend to suspect that Oracle would use that mapping to determine which UNDO segments are old enough that they should be purged and that you would start to encounter problems if you exceeded that limit.

On my 11.2 system, for example, I can convert 7 days ago to an SCN and then back to a timestamp but not 8 days ago

  1* select scn_to_timestamp( timestamp_to_scn( systimestamp - 7) ) from dual
SQL> /

SCN_TO_TIMESTAMP(TIMESTAMP_TO_SCN(SYSTIMESTAMP-7))
---------------------------------------------------------------------------
06-NOV-12 01.06.47.000000000 PM

SQL> ed
Wrote file afiedt.buf

  1* select scn_to_timestamp( timestamp_to_scn( systimestamp - 8) ) from dual
SQL> /
select scn_to_timestamp( timestamp_to_scn( systimestamp - 8) ) from dual
                         *
ERROR at line 1:
ORA-08180: no snapshot found based on specified time
ORA-06512: at "SYS.TIMESTAMP_TO_SCN", line 1

I would tend to suspect on my system, therefore, that if I tried to retain 8 days of UNDO that Oracle would generally have a problem complying. It would take a week to verify one way or the other but if I had to wager, I would tend to wager that trying to hold more than a week of UNDO would fail.

Even if you could hold the UNDO that long, formulating the flashback query would be a major challenge since you couldn't specify a timestamp. You could potentially maintain your own (more long-term) mapping between SCN and timestamp by writing an automated job that would capture both every few seconds and hold them for weeks or months at a time and then specify AS OF SCN in your flashback queries. I'd tend to expect that the older UNDO would be gone but it might work.

You can declare table as NOLOGGING. Then some of the DML and DDL operations will not be recorded in redo logs.

Oracle logging_clause

Of course this has many caveats:

• if you have many NOLOGGING operations basically you won't be able to flashback or restore the table
• UPDATE or DELETE operations are logged in any case
• only direct path INSERTS can skip logging. But that means that such INSERT will not reuse empty space in table segment and will extend it every time you do such INSERT.

In my experience in some situations one can save a lot of redo/archivelog space using NOLOGGING but you have to adapt your code to it.