Sizing Undo Tablespace
                                                 Last update (2008-02-28 12:50:02)
                                                                                                                   Date added (2008-02-28 12:00:59)

Summary
The sizing of UNDO tablespace is simple. First you must answer the question:

You want Undo recorded data to be kept in your database for at least x time in seconds?
or you can affort specific space and only that for Undo?


Some information about Undo
V$UNDOSTAT displays a histogram of statistical data to show how well the system is working. The available statistics include undo space consumption, transaction concurrency, and length of queries executed in the instance. You can use this view to estimate the amount of undo space required for the current workload. Oracle uses this view to tune undo usage in the system. This view is available in both automatic undo management mode and manual undo management mode.

Each row in the view keeps statistics collected in the instance for a 10-minute interval. The rows are in descending order by the BEGIN_TIME column value. Each row belongs to the time interval marked by (BEGIN_TIME, END_TIME). Each column represents the data collected for the particular statistic in that time interval. The first row of the view contains statistics for the (partial) current time period. The view contains a total of 1008 rows, spanning a 7 day cycle.

So based on the above information, if you decide that you want absolutely for at least x seconds to flashback(undo_retention), depending on your Number of undo data blocks generated per second from your system, the following space required.
SELECT d.undo_size/(1024*1024) YOU_HAVE_UNDO_MB,
SUBSTR(e.VALUE,1,25) "UNDO RETENTION (Secs)", 
ROUND((TO_NUMBER(e.VALUE) * TO_NUMBER(f.VALUE) * g.undo_block_per_sec) / (1024*1024),2) YOU_NEED_UNDO_MB
FROM (SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.CONTENTS = 'UNDO' 
AND c.status = 'ONLINE'
AND b.NAME = c.tablespace_name
AND a.ts# = b.ts#) d,
v$parameter e,
v$parameter f,
(SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) undo_block_per_sec
FROM v$undostat) g
WHERE e.NAME = 'undo_retention'
AND f.NAME = 'db_block_size';
Tip: Change the undo_retention parameter and query again to see what space is required for UNDO, for example 10 hours
ALTER SYSTEM SET undo_retention = 36000 SCOPE=BOTH;
If you can affort specific volume of space and only that for UNDO, then first create the UNDO datablespace with specific size and then adjust the undo_retention to a value until the last column in the previous query is approximately the size you have.
Reviews
Categories
Oracle DBA-> (147)
  Alertlog (3)
  Archivelog (5)
  Auditing (1)
  Backup - Recovery (19)
  Database files (6)
  Database options (2)
  DB links (2)
  Dependencies (1)
  Dictionary (2)
  Exp-Imp Datapump (6)
  Jobs (2)
  Mview (2)
  Networking (3)
  Objects (7)
  Parameters (2)
  Redologs (6)
  Roles - Grants (2)
  Rollback - Undo (8)
  Segments (3)
  Sequences (2)
  Sessions (14)
  SGA (14)
  Tables (10)
  Tablespaces (10)
  Temp (4)
  Toad (5)
  Transactions (1)
  Upgrade (2)
  Users (3)
Oracle APPS DBA-> (66)
Exadata (1)
Performance Tuning-> (59)
Oracle Real Cases (24)
Oracle Errors (23)
Oracle SQL tricks (32)
Oracle RAC (3)
Oracle Security (8)
Filters
Search