Re: Guaranteed Flashback Table

Sorry, you're absolutely right - I've commited the oracle-l sin of forgetting 
to mention the version - I'm on 10gR2.

The problem I have with RETENTION GUARANTEE is that I've absolutely no idea how 
long the data manipulation will need - there's not just processing time but 
time involved in getting results manually checked, waiting for people to do 
this (who tend to wander off out of town when you most need them etc.)  so I'm 
scared of setting too small a value that we can't then flash back, or too large 
a value that we blow out of disk space capturing lots of un-needed undo for the 
rest of the database not involved in this operation.

Flashback Archive sounds like it could be just the ticket - but no plans for 
11g just yet.   Thanks, though, I'll keep it in mind for the future!

Thank you,
Charlotte


----- Original Message ----
From: Robert Freeman <robertgfreeman@xxxxxxxxx>
To: knecht.stefan@xxxxxxxxx; charlottejanehammond@xxxxxxxxx
Cc: oracle-l <oracle-l@xxxxxxxxxxxxx>
Sent: Wednesday, April 30, 2008 11:20:53 PM
Subject: Re: Guaranteed Flashback Table


OF course, she could be on 9iR1 and then none of this makes a difference! :-)

 
Robert G. Freeman
Author:
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
Blog: http://robertgfreeman.blogspot.com (Oracle Press)



----- Original Message ----
From: Stefan Knecht <knecht.stefan@xxxxxxxxx>
To: charlottejanehammond@xxxxxxxxx
Cc: oracle-l <oracle-l@xxxxxxxxxxxxx>
Sent: Wednesday, April 30, 2008 3:16:56 PM
Subject: Re: Guaranteed Flashback Table

Hi Charlotte

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:

From: 
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_7003.htm#i2231734

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.

Cheers

Stefan




On Wed, Apr 30, 2008 at 10:01 PM, Charlotte Hammond 
<charlottejanehammond@xxxxxxxxx> wrote:

Hi All,

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!
Charlotte






     
____________________________________________________________________________________
Be a better friend, newshound, and
know-it-all with Yahoo! Mobile.  Try it now.  
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
--
http://www.freelists.org/webpage/oracle-l






-- 
=========================

Stefan P Knecht
Senior Consultant
Infrastructure Managed Services

Trivadis AG
Europa-Strasse 5
CH-8152 Glattbrugg

Phone +41-44-808 70 20
Fax +41-808 70 12
Mobile +41-79-571 36 27
stefan.knecht@xxxxxxxxxxxx
http://www.trivadis.com

OCP 9i/10g SCSA SCNA
========================= 


      
____________________________________________________________________________________
Be a better friend, newshound, and 
know-it-all with Yahoo! Mobile.  Try it now.  
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ

Other related posts: