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';

No comments:

Post a Comment

View running queries in postgresql

 View running queries in postgreSQL: SELECT * FROM pg_stat_activity;