How to check actual undo size and needed undo size in oracle

How to check actual undo size and needed undo size in oracle
===============================================


select d.undo_size/(1024*1024) "ACTUAL UNDO SIZE (MEGS)",
  (to_number(e.value) * to_number(f.value) * g.undo_block_per_sec) /
  (1024*1024) "NEEDED UNDO SIZE (MEGS)"
  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';

Comments

Popular posts from this blog

SQL Tuning Task Creation ORA-13780: SQL statement does not exist.

Shareplex some commands

ORA-1092 : opitsk aborting process