Discussion:
Flashback and UNDO_RETENTION
(too old to reply)
Michael42
2006-10-07 16:32:29 UTC
Permalink
Hello,

In an Oracle 10g R2 database on Solaris 10 I have a table that is
150gb. Could you please help me determine how big my UNDO_RETENTION
should be set to:

1. To be able to use Flashback Query on this table for up to 24 hours.
2. To be able to use Flashback Dropped table on this table for up to 24
hours.

Thanks very much,

Michael42
DA Morgan
2006-10-07 17:27:59 UTC
Permalink
Post by Michael42
Hello,
In an Oracle 10g R2 database on Solaris 10 I have a table that is
150gb. Could you please help me determine how big my UNDO_RETENTION
1. To be able to use Flashback Query on this table for up to 24 hours.
2. To be able to use Flashback Dropped table on this table for up to 24
hours.
Thanks very much,
Michael42
desc gv_$undostat
--
Daniel A. Morgan
University of Washington
***@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Brian Peasland
2006-10-08 18:41:17 UTC
Permalink
Post by Michael42
Hello,
In an Oracle 10g R2 database on Solaris 10 I have a table that is
150gb. Could you please help me determine how big my UNDO_RETENTION
1. To be able to use Flashback Query on this table for up to 24 hours.
2. To be able to use Flashback Dropped table on this table for up to 24
hours.
Thanks very much,
Michael42
If you need to be able to perform Flashback Query for any time in the
past 24 hours, then UNDO_RETENTION should be set to at least 86,400 (24
hours * 60 minutes/hr * 60 seconds/min).

Flashback Drop does not use the UNDO tablespace. Rather, it used the
Recycle Bin.

HTH,
Brian
--
===================================================================

Brian Peasland
***@nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown
Michael42
2006-10-09 11:04:21 UTC
Permalink
Dan, Brian,

Thanks very much for the responses. Very useful!

In addition to setting the UNDO_RETENTION, how would one determine the
size required for the UNDO tablespace (if this is even a factor in this
case)?

Thanks again,

Michael42
Matthias Hoys
2006-10-09 20:51:56 UTC
Permalink
Post by Michael42
Dan, Brian,
Thanks very much for the responses. Very useful!
In addition to setting the UNDO_RETENTION, how would one determine the
size required for the UNDO tablespace (if this is even a factor in this
case)?
Thanks again,
Michael42
By looking at the V$UNDOSTAT dynamic view.
Example : http://www.akadia.com/services/ora_optimize_undo.html.


Matthias

Loading...