SEO

#1 (**permalink**) 02-25-2008, 07:39 AM

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

#2 (**permalink**) 02-25-2008, 07:40 AM

Michael42 wrote:
> 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

desc gv_$undostat
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#3 (**permalink**) 02-25-2008, 07:40 AM

Michael42 wrote:
> 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
>

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
dba@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

#4 (**permalink**) 02-25-2008, 07:41 AM

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

#5 (**permalink**) 02-25-2008, 07:42 AM

"Michael42" <melliott42@yahoo.com> wrote in message
news:1160391861.301788.146110@c28g2000cwb.googlegr oups.com...
> 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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Flashback and UNDO_RETENTION