set lines 120
set pages 999
clear col
set termout off
set trimout on
set trimspool on
connect / as sysdba
alter session set nls_date_format='dd-hh24:mi';
spool undohistoryinfo.out
prompt
prompt ############## RUNTIME ##############
prompt
col rdate head "Run Time"
select sysdate rdate from dual;
prompt
prompt ############## HISTORICAL DATA ##############
prompt
col x format 999,999 head "Max Concurrent|Last 7 Days"
col y format 999,999 head "Max Concurrent|Since Startup"
--最大的并发
select max(maxconcurrency) x from v$undostat
/
--历史最大并发
select max(maxconcurrency) y from sys.wrh$_undostat
/
col i format 999,999 head "1555 Errors"
col j format 999,999 head "Undo Space Errors"
--出现01555的总次数
select sum(ssolderrcnt) i from v$undostat
where end_time > sysdate-2
/
--出现no spaace错误的次数
select sum(nospaceerrcnt) j from v$undostat
where end_time > sysdate-2
/
clear break
clear compute
prompt
prompt ############## CURRENT STATUS OF SEGMENTS ##############
prompt ############## SNAPSHOT IN TIME INFO ##############
prompt ##############(SHOWS CURRENT UNDO ACTIVITY)##############
prompt
col segment_name format a30 head "Segment Name"
col "ACT BYTES" format 999,999,999,999 head "Active Bytes"
col "UNEXP BYTES" format 999,999,999,999 head "Unexpired Bytes"
col "EXP BYTES" format 999,999,999,999 head "Expired Bytes"
select segment_name,
sum(case
when status = 'ACTIVE' then
bytes
else
0
end) "ACT BYTES", --活动状态的区大小
sum(case
when status = 'UNEXPIRED' then
bytes
else
0
end) "UNEXP BYTES" , --未过期区大小
sum(case
when status = 'EXPIRED' then
bytes
else
0
end) "EXP BYTES" --过期区大小
from dba_undo_extents
group by segment_name
order by 1
prompt
prompt ############## UNDO SPACE USAGE ##############
prompt
col usn format 999,999 head "Segment#" --回滚段编号
col shrinks format 999,999,999 head "Shrinks"
col aveshrink format 999,999,999 head "Avg Shrink Size"
select usn, --回滚段编号
shrinks, --回滚段收缩次数
aveshrink --平均每次收缩的大小
from v$rollstat
spool off
set termout on
set trimout off
set trimspool off
clear col
UndoHistoryInfo.sql