undo详细信息以及大事务回滚计算时间脚本

 今天是2021-3-26日，从网上看到2个sql比较好用。特此记录，方便后期好查。

1、检查undo详细信息情况。

set pages 1000
set line 1000
set feedback off
set serveroutput on 

-- UNDO Check script start
exec dbms_output.put_line('== >>>>>>>>>> UNDO CHECK SCRIPT START <<<<<<<<<< ==');
exec dbms_output.put_line(' ');

-- Check  database version
PROMPT Checking database version......
PROMPT ============
select * from v$version;
exec dbms_output.put_line(' ');
exec dbms_output.put_line(' ');

-- Check if flashback on
PROMPT Checking if flashback on......
PROMPT ============
select flashback_on from v$database;
exec dbms_output.put_line(' ');
exec dbms_output.put_line(' ');

-- Check  the undo parameter
PROMPT Checking the undo parameter......
PROMPT ============
show parameter undo;
exec dbms_output.put_line(' ');
exec dbms_output.put_line(' ');


-- Check undo tablespace usage
PROMPT Checking undo tablespace usage......
PROMPT ============
SELECT d.status ,
       d.tablespace_name ,
       d.contents,
       d.extent_management,
       to_char(nvl(a.bytes / 1024 / 1024, 0), '99999999.999') as total_size_mb,
       to_char(nvl(a.bytes - nvl(f.bytes, 0), 0) / 1024 / 1024,
               '99999999.999') used_size_mb,
       to_char(nvl(nvl(f.bytes, 0), 0) / 1024 / 1024, '99999999.999') free_size_mb,
       to_char(nvl((a.bytes - nvl(f.bytes, 0)) / a.bytes * 100, 0),
               '990.00') as used_percent
  FROM sys.dba_tablespaces d,
       (SELECT tablespace_name, SUM(bytes) bytes
          FROM dba_data_files
         GROUP BY tablespace_name) a,
       (SELECT tablespace_name, SUM(bytes) bytes
          FROM dba_free_space
         GROUP BY tablespace_name) f
 WHERE d.tablespace_name = a.tablespace_name(+)
   AND d.tablespace_name = f.tablespace_name(+)
   AND d.contents='UNDO';
exec dbms_output.put_line(' ');
exec dbms_output.put_line(' ');


-- Check rollback segment status
PROMPT Checking rollback segment status......
PROMPT ============
select tablespace_name,status,sum(bytes)/1024/1024 mb from DBA_UNDO_EXTENTS group by tablespace_name,status;
exec dbms_output.put_line(' ');
exec dbms_output.put_line(' ');


--Check all roll segment online or offline status
PROMPT Checking Check all roll segment online or offline status......
PROMPT ============
select substr(segment_name,1,7) as rollname,status,count(*) from dba_rollback_segs
group by substr(segment_name,1,7),status
order by 1;
exec dbms_output.put_line(' ');
exec dbms_output.put_line(' ');


--Check online roll segment status
PROMPT Checking roll segment online status......
PROMPT ============
SELECT
substr(name,1,7) as rollname,
status,count(*) as cnt
FROM v$rollstat, v$rollname
WHERE v$rollstat.usn=v$rollname.usn
group by 
substr(name,1,7),status order by 1;
exec dbms_output.put_line(' ');
exec dbms_output.put_line(' ');


-- Check TOP 20 roll segment extend status
PROMPT Checking TOP 20 roll segment extend status......
PROMPT ============
select * from (
SELECT
ds.segment_name "Seq Name",
ds.bytes "Bytes",
ds.blocks "Blocks",
ds.extents "Extents",
ds.initial_extent "Init Ext",
ds.next_extent "Next Ext",
ds.min_extents "Min Ext",
ds.max_extents "Max Ext"
FROM dba_segments ds
WHERE segment_type in ('ROLLBACK','TYPE2 UNDO') order by extents desc) where rownum<=20;
exec dbms_output.put_line(' ');
exec dbms_output.put_line(' ');


-- Check undo header wait
PROMPT Checking Undo Header Waits......
PROMPT (Note:Need to wait 60Secs)
PROMPT ============
 declare
  s_num_rbs number;
  s_header_wait number;
  s_waits_per_rbs number;
  e_num_rbs number;
  e_header_wait number;
  e_waits_per_rbs number;
  delta_num_rbs number;
  delta_header_wait number;
  delta_waits_per_rbs varchar2(200);
  s_mydate   varchar2(200);
  e_mydate   varchar2(200);
  v_pause_secs number;
  begin  
  --Set the pause time for get 2 times change,default is 30 seconds  
  v_pause_secs:=60;
  select to_char(sysdate,'hh24:mi:ss') into s_mydate from dual;  
  select COUNT(stat.USN) ,wait.Count,round(wait.Count/COUNT(stat.USN),4) into s_num_rbs,s_header_wait,s_waits_per_rbs from V$WAITSTAT wait, V$ROLLSTAT stat where stat.Status = 'ONLINE' and wait.Class = 'undo header' and stat.USN > 0 group by wait.Count;
  dbms_lock.sleep(v_pause_secs);
  select to_char(sysdate,'hh24:mi:ss') into e_mydate from dual; 
  select COUNT(stat.USN),wait.Count,round(wait.Count/COUNT(stat.USN),4) into e_num_rbs,e_header_wait,e_waits_per_rbs from V$WAITSTAT wait, V$ROLLSTAT stat where stat.Status = 'ONLINE' and wait.Class = 'undo header' and stat.USN > 0 group by wait.Count;
  select (e_num_rbs-s_num_rbs),(e_header_wait-s_header_wait),to_char(round(nvl(decode((e_waits_per_rbs-s_waits_per_rbs),0,null,(e_waits_per_rbs-s_waits_per_rbs)),0),4),'fm999999990.999999999') into delta_num_rbs,delta_header_wait,delta_waits_per_rbs  from dual;
    dbms_output.put_line(s_mydate||'==> At start time:');
    dbms_output.put_line('Number of Rollback segments:'||s_num_rbs||' ,Number of Undo header waits:'||s_header_wait||' ,Number of header wait per Rollback segment:'||s_waits_per_rbs);
    dbms_output.put_line(e_mydate||'==> At end time:');
    dbms_output.put_line('Number of Rollback segments:'||e_num_rbs||' ,Number of Undo header waits:'||e_header_wait||' ,Number of header wait per Rollback segment:'||e_waits_per_rbs);
    dbms_output.put_line('==== Change during '||v_pause_secs||' Seconds:====');
    dbms_output.put_line('Delta of Rollback segments:'||delta_num_rbs||' ,Delta of Undo header waits:'||delta_header_wait||' ,Delta of header wait per Rollback segment:'||delta_waits_per_rbs);
  end;
/
exec dbms_output.put_line(' ');
exec dbms_output.put_line(' ');

-- Check latch: undo global data
PROMPT Checking TOP 20 "latch: undo global data"......
PROMPT ============
select * from (
select to_char(end_interval_time,'yyyy-mm-dd hh24:mi') as end_interval_time,
       nvl(round((c.time_waited_micro - lag(time_waited_micro)
        over(order by c.snap_id)) /
       decode((c.total_waits - lag(total_waits) over(order by c.snap_id)),0,null,(c.total_waits - lag(total_waits) over(order by c.snap_id)))/1000,2),0) as avg_wait_time_ms
  from dba_hist_system_event c, dba_hist_snapshot dd
 where event_name = 'latch: undo global data' and c.instance_number=dd.instance_number
   and c.snap_id = dd.snap_id and c.instance_number=1
   and c.snap_id >= 1 and end_interval_time>=trunc(sysdate-7)
   order by 1 desc)
   where avg_wait_time_ms<>0 and rownum<=20;
exec dbms_output.put_line(' ');
exec dbms_output.put_line(' ');

-- Check location of undo latch undo global data 
PROMPT Checki