Oracle Undo, Temp & RMAN Sizing Calculator

Need a capacity or backup review? Book a consultation →

1. Sizing the UNDO tablespace

Oracle's own guidance sizes undo from three things: how long you need to keep undo (UNDO_RETENTION), how fast the database generates undo (undo blocks per second), and the block size:

UNDO size = UNDO_RETENTION × undo_blocks_per_sec × DB_BLOCK_SIZE

To get your real undo rate, query V$UNDOSTAT (each row is a ~10-minute snapshot):

SELECT MAX(undoblks / ((end_time - begin_time) * 86400)) AS peak_ups
FROM v$undostat;

Feed that peak into the calculator, keep a safety buffer, and if you rely on Flashback Query you may want RETENTION GUARANTEE on the tablespace — just be aware it will let transactions fail with ORA-30036 rather than overwrite unexpired undo, so it needs headroom. If you regularly hit ORA-01555 "snapshot too old", either the retention or the tablespace is too small for your longest-running queries. (I wrote a full walkthrough on that in the undo, redo & ORA-01555 guide.)

2. A starting point for TEMP

TEMP is harder to pin to a single formula because it depends entirely on your workload — big sorts, hash joins, global temporary tables and index rebuilds all draw from it. A practical planning approach is to multiply the largest work area a heavy query uses by how many such queries run at once, then add headroom:

TEMP ≈ max_work_area × concurrent_heavy_sessions × (1 + headroom)

Treat the result as a floor, not gospel. Watch V$TEMPSEG_USAGE and V$SORT_USAGE during your real peak (month-end reports, big batch jobs, index maintenance), and configure TEMP with autoextend plus a firm MAXSIZE so a single runaway sort cannot fill the disk.

3. Planning RMAN backup / FRA storage

Your Fast Recovery Area has to hold enough to satisfy your recovery window — the number of days back you must be able to restore to. The calculator adds up three pieces:

• Full backups — at least one full older than the window, plus current, each shrunk by compression and by RMAN skipping empty blocks.
• Incrementals — roughly your daily changed data, compressed, across the window.
• Archived redo — everything generated since the oldest backup you keep; often larger than the changed-data figure because every change writes redo.

Set DB_RECOVERY_FILE_DEST_SIZE comfortably above the estimate, keep a RECOVERY WINDOW OF n DAYS retention policy, and let DELETE OBSOLETE reclaim space. Undersizing the FRA is a classic 3 a.m. outage — archiver stops, and the database hangs. For the full backup-and-recovery playbook, see my RMAN backup & recovery guide.

How do I calculate the right UNDO tablespace size in Oracle?

Multiply your undo retention target (in seconds) by the peak undo blocks generated per second and by the database block size: UNDO size = UNDO_RETENTION × undo_blocks_per_sec × DB_BLOCK_SIZE. Get the undo rate from V$UNDOSTAT, add a safety buffer of 15–30%, and set UNDO_RETENTION to your target. This calculator does that math for you.

What is a good UNDO_RETENTION value?

Set UNDO_RETENTION at least as long as your longest-running query or the furthest-back Flashback Query you need. 900 seconds (15 minutes) is a common starting point for OLTP, but reporting or batch systems with long queries often need much more. If you see ORA-01555 "snapshot too old", your retention or undo tablespace is too small for your query duration.

How big should my TEMP tablespace be?

There is no single formula — TEMP depends on concurrent sorts, hash joins and global temporary tables. A practical starting point is the largest work area a heavy query uses multiplied by the number of such queries running at peak, plus headroom. Then validate against V$TEMPSEG_USAGE and V$SORT_USAGE under real load and use autoextend with a MAXSIZE.

How do I estimate RMAN backup storage or FRA size?

Add up the full backups you must keep for your recovery window, the incremental backups (roughly daily changed data), and the archived redo generated across the window — each reduced by compression. Set DB_RECOVERY_FILE_DEST_SIZE above that total and monitor V$RECOVERY_FILE_DEST. The RMAN tab of this calculator estimates all three for you.

Does the calculator send my numbers anywhere?

No. All three calculators run entirely in your browser with plain JavaScript. Nothing you type is uploaded, logged or stored — it is safe to use with production planning figures.

Are these numbers exact?

They are informed planning estimates, not guarantees. Real undo, temp and backup sizes vary with block density, deletes, redo volume and workload spikes. Use the results as a defensible starting point, then confirm against your own database's dynamic performance views before you commit storage.