根据metalink脚本学习undo（五）

来源：互联网发布：小学生编程比赛编辑：程序博客网时间：2024/05/21 16:40
set lines 120set pages 999clear colset termout offset trimout onset trimspool onconnect / as sysdbaalter session set nls_date_format='dd-hh24:mi';spool undohistoryinfo.outpromptprompt  ############## RUNTIME ############## promptcol 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$undostatwhere end_time > sysdate-2/--出现no spaace错误的次数select sum(nospaceerrcnt) j from v$undostatwhere end_time > sysdate-2/clear breakclear computeprompt 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 offset termout onset trimout offset trimspool offclear col
UndoHistoryInfo.sql
0 0