Is it possible to tune this statement without having to use a rule
hint?
select 'DATABASENAME: ORA_UNDO_SPACE: UNDO_AU: '||round(f.undo_au)||',
UNDO_SIZE: '||round(g.undo_size)|| ' MB used in
%:'||round((f.undo_au/g.undo_size) * 100,2)||' %. Please check!' from
(select nvl( sum(sumbytes), 0 ) undo_au from (select status,
sum(bytes)/1024/1024 sumbytes, count(*) from dba_undo_extents group by
status ) sumundo
where status in ('ACTIVE','UNEXPIRED')) f,
(SELECT sum((VDF.bytes)/1024/1024) undo_size
FROM v$datafile VDF, v$tablespace VTS, dba_tablespaces DTS
WHERE DTS.status = 'ONLINE'
AND DTS.contents = 'UNDO'
AND VTS.ts# = VDF.ts#
AND DTS.tablespace_name = VTS.name) g
where (f.undo_au/g.undo_size) > 0.7;
Received on Wed Jan 11 2006 - 03:11:34 CST
