RETENTION GUARANTEE may be the answer to her problem, yes.
RETENTION GUARANTEE and flashback archive
tablespaces are very different animals though. If you use retention guarantee
and the undo tablespace fills up, then you start having wide scale
database problems with DML across all database objects. Probably not
the best of all worlds.
With flashback archive tablespaces, you don't have this wide scale
problem. Flashback data is retained only for the objects assigned to
the tablespace. If you run out of space, then it's only the objects
assigned to that flashback archive (not the tablespace) that will have
issues with DML.
Certainly RETENTION GUARANTEE can be used in her case, but with wide scale risks to all database DML. Flashback archives eliminates this risk considerably.
Robert G. Freeman
Oracle Database 11g New Features (Oracle Press)
Portable DBA: Oracle (Oracle Press)
Oracle Database 10g New Features (Oracle Press)
Oracle9i RMAN Backup and Recovery (Oracle Press)
Oracle9i New Feature
Original Message ----
From: Stefan Knecht
Sent: Wednesday, April 30, 2008 3:16:56 PM
Subject: Re: Guaranteed Flashback Table
No need to go to 11g -- you can do this in 10gR2 as well -- take a look at the retention guarantee clause, which you can specifiy at tablespace level:
RETENTION GUARANTEE specifies that Oracle Database should preserve unexpired undo data in all undo segments of tablespace even if doing so forces the failure of ongoing operations that need
undo space in those segments. This setting is useful if you need to
issue an Oracle Flashback Query or an Oracle Flashback Transaction
Query to diagnose and correct a problem with the data.
On Wed, Apr 30, 2008 at 10:01 PM, Charlotte Hammond wrote:
I'd like to have the option to flashback a few large-ish tables (NOT the whole database) to the start of a complex data manipulation operation. However I have no idea how long this operation will take so I have to set UNDO_RETENTION to a big value just to be sure. I'd prefer to be able to define a point at the start of the operation, and say "keep enough undo to go back here". Is there any such mechanism? (Only thing I could think of was to keep dynamically cranking up UNDO_RETENTION as the operation proceeds but that seems really messy).
I'll probably just stick with the tried and trusted CTAS "backup" of the tables before I start but just wanted to check that I'm not missing out on something that could save me time and space.
Any suggestions welcome - thanks!
Be a better friend, newshound, and
know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ