What should be the size of UNDO tablespace? When it is not sized properly, you will encounter "ORA-01555: snapshot too old: rollback segment number %n with name "%segname" too small." error.
-- Estimate the size of UNDO tablespace. This will help DBA to get the size of UNDO tablespace by using a simple formula.
-- Transaction Rate * db_block_size * Preferred Undo Retention
-- 1. Get the highest Undo Blocks
SQL> select to_char(BEGIN_TIME , 'mm/dd/yyhh24:mi') "Begin Time", to_char(END_TIME, 'mm/dd/yyhh24:mi') "End Time", UNDOBLKS from v$undostat
where UNDOBLKS = (select max(UNDOBLKS) from v$undostat)
;
-- 2. Divide the highest block to 600 (seconds)Begin Time End Time UNDOBLKS
------------- ------------- ----------
01/31/1205:32 01/31/1206:29 3340
SQL> select 3340/600 from dual;
-- 3. Get the value of db_block_size3340/600
----------
5.56666667
SQL> show parameter db_block_size;
-- 4. Get the value of db_block_sizeNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
SQL> show parameter undo_retention;
-- 5. Transaction Rate * db_block_size * Preferred Undo RetentionNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_retention integer 16554
SQL> select 3340/600 * 8192 * 16554 / 1024 /1024 "Optimal Size MB" from dual;
-- Here is a more comprehensive explanation.Optimal Size MB
---------------
719.926563
Putting them all together, use the below query to get the optimal size for your Undo Tablespace for the given Undo Retention and the highest Undo Blocks
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
(TO_NUMBER(e.value) * TO_NUMBER(f.value) *
g.undo_block_per_sec) / (1024*1024)
"NEEDED UNDO SIZE [MByte]"
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'
/
Note: As you can see, the actual size set for your Undo Tablespace is not enough. You need to increase your UNDO tablespace size:ACTUAL UNDO SIZE [MByte] UNDO RETENTION [Sec] NEEDED UNDO SIZE [MByte]
------------------------ ------------------------- ------------------------
485 16554 719.926563
SQL> alter tablespace UNDO add datafile '/Full/Undo/Datafile/Path' size 720M;
-- To get the datafiles for your Undo Tablespace, you can use the below query:
------------------set pagesize 200
col tablespace_name for a20 heading "Tablespace Name"
col file_name format a50 heading "Datafile"
col total_bytes format 999,999,999,999.9 heading "Total|Bytes|(MB)"
col bytes_free format 999,999,999.9 heading "Bytes|Free|(MB)"
col bytes_used format 999,999,999.9 heading "Bytes|Used|(MB)"
col percent_free format 9999 heading "% Free"
col percent_used format 9999 heading "% Used"
rem break on report
break on tablespace_name skip 1 on report
compute sum of total_bytes bytes_free bytes_used on tablespace_name
compute sum label "Grand Total" of total_bytes bytes_free bytes_used on report
-- compute avg of percent_free percent_used on tablespace_name
set linesize 160
TTitle left skip 2 "****** Tablespace Space Utilization *******" skip 2
select df.tablespace_name
,df.file_name
,df.bytes/1024/1024 total_bytes
,NVL(sum(fs.bytes/1024/1024),0) bytes_free
,NVL((100*((sum(fs.bytes))/df.bytes)),0) percent_free
,(df.bytes-nvl(sum(fs.bytes),0))/1024/1024 bytes_used
,(100*((df.bytes-nvl(sum(fs.bytes),0))/df.bytes)) percent_used
from sys.dba_data_files df
,sys.dba_free_space fs
where df.file_id = fs.file_id(+)
and df.tablespace_name = fs.tablespace_name(+)
group by df.file_name
,df.tablespace_name
,df.bytes
union all
select tf.tablespace_name
,tf.file_name
,tf.bytes/1024/1024 total_bytes
,sum(sh.bytes_free/1024/1024) bytes_free
,(100*((sum(sh.bytes_used))/tf.bytes)) percent_free
,(tf.bytes-sum(sh.bytes_used))/1024/1024 bytes_used
,(100*((tf.bytes-sum(sh.bytes_used))/tf.bytes)) percent_used
from sys.dba_temp_files tf
,v$temp_space_header sh
where tf.file_id = sh.file_id(+)
and tf.tablespace_name = sh.tablespace_name(+)
group by tf.file_name
,tf.tablespace_name
,tf.bytes
order by 1,2;
TTitle off
-- To view who is using UNDO Tablespace, please follow this thread: http://www.oracle-forums.com/who-is-using-undo-t20.html
-- View Undo Transactions
select distinct OWNER,TABLESPACE_NAME,BYTES/1024/1024, STATUS from dba_undo_extents;
OWN Tablespace Name BYTES/1024/1024 STATUS
--- -------------------- --------------- ---------
SYS UNDOTBS1 .0625 EXPIRED
SYS 1 EXPIRED