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';
===============================================
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
Post a Comment