How to calculate the size of the Undo tablespace In the Undo Series

Insufficient undo space may cause a ORA-30036 or ORA-01555.

We create a small undo tablespace and execute a large transaction, then we can simulate the ORA-30036.

Sys @ orcl> Create undo tablespace thinktbs datafile '/u01/APP/Oracle/oradata/orcl/datafile/think_undo.dbf' 2 size 2 m autoextend off; tablespace created. sys @ orcl> alter system set undo_tablespace = thinktbs; System altered. sys @ orcl> show parameter undoname type value =----------- export undo_management string autoundo_retention integer 800undo_tablespace string thinktbs another session: HR @ orcl> select count (*) from t; count (*) ---------- 462140hr @ orcl> begin 2 for I in 1 .. 1000 3 Loop 4 Delete from t where rownum <1001; 5 commit; 6 end loop; 7 end; 8/begin * error at line 1: ORA-30036: unable to extend segment by 8 in undo tablespace 'thinktbs 'ora-06512: At Line 4 during the execution of this transaction, we can query how many undo data blocks it uses: sys @ orcl> select ADDR, used_ublk from V $ transaction; ADDR used_ublk -------- 37a19440 1937a19e88 1

How can we determine the size of the Undo tablespace? You can use em for management.
The size of the Undo tablespace has three factors:
1) undo_retention: this value is determined, see my blog: http://blog.csdn.net/linwaterbin/article/details/7800190
2) undo data blocks per second: We can query this value from V $ undostat to calculate the maximum value or average value, but we recommend that you take the maximum value.
3) default block size
Formula
Undo size = db_block_size * undo_retention * max (undoblks/(end_time-begin_time) * 24*3600 ))

sys@ORCL> select max(undoblks/((end_time-begin_time)*24*3600)) from v$undostat;MAX(UNDOBLKS/((END_TIME-BEGIN_TIME)*24*3600))---------------------------------------------                                   5.77333333sys@ORCL> show parameter db_block_sizeNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------db_block_size                        integer     8192sys@ORCL> show parameter undo_retentionNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------undo_retention                       integer     800

Through the above query, we can calculate the specific value of the Undo tablespace size. We can increase the size of the Undo tablespace accordingly.

sys@ORCL> alter tablespace undotbs1 add datafile '/u01/app/oracle/oradata/ORCL/datafile/thinkundo.dbf' size 30m autoextend on;Tablespace altered.