Database Administration (MOSC)

MOSC Banner

Sizing undo tablespace

edited Feb 3, 2010 8:05AM in Database Administration (MOSC) 17 commentsAnswered

 According to the document 262066.1 How to Size UNDO Tablespace For Automatic Undo Management, I issue the query for one of our databases that use undo tablespace.

SQL> spool check_undo_block_size.rpt
SQL> select (UR *(UPS*DBS)) + (DBS*24) AS "Bytes"
  2  from (select value AS UR FROM v$parameter WHERE name ='undo_retention'),
  3  (select(SUM(undoblks)/SUM(((end_time - begin_time)*86400))) AS UPS FROM v$undostat),
  4  (select block_size as DBS from dba_tablespaces where tablespace_name =
  5  (select upper(value) from v$parameter where name ='undo_tablespace'));

     Bytes
----------
4089507270

1 row selected.

This is the results I got. Supposely if I set the undo_tablespace to 4G according to the query, the database should be happy. The actual space I gave when I create the undo was 8G. I thought this should be more than sufficient than the recommended 4G. However, I still sometimes get the space fill up error. My undo_retention is set at 72000 which is about 20 hours. My block_size is 8192 and UPS is 6.9 blocks per seconds. Does that mean I need to add more space? Any suggestions?

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center