根据metalink脚本学习undo（三）

UndoUsage.sql 关于UNDO使用率 使用状态的脚本

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-Mon-yyyy hh24:mi';

spool undousage.out

prompt
prompt  ############## RUNTIME ############## 
prompt

col rdate head "Run Time"

select sysdate rdate from dual;

prompt 
prompt  ############## IN USE Undo Data ############## 
prompt 
--undo 使用率
select 
((select (nvl(sum(bytes),0)) 
from dba_undo_extents 
where tablespace_name in (select tablespace_name from dba_tablespaces
   where retention like '%GUARANTEE' )
and status in ('ACTIVE','UNEXPIRED')) *100) / 
(select sum(bytes) 
from dba_data_files 
where tablespace_name in (select tablespace_name from dba_tablespaces
   where retention like '%GUARANTEE' )) "PCT_INUSE" 
from dual; 

--undo 表空间信息
select tablespace_name,   --undo表空间名称
       extent_management, --extent 管理模式  
       allocation_type,   --分配方式  SYSTEM UNIFORM USER
       segment_space_management,  --段管理方式
       retention          -- GUARANTEE  undo 表空间声明为GUARANTEE   强制保留undo信息到undo_RETENTION
                          -- GUARANTEE  undo 表空间声明为NOGUARANTEE 尽量保留undo信息到undo_RETENTION
                          -- NOT APPLY  非undo 表空间 
from dba_tablespaces where retention like '%GUARANTEE' 
/

col c format 999,999,999,999 head "Sum of Free"

select (nvl(sum(bytes),0)) c from dba_free_space --剩余空间
where tablespace_name in
(select tablespace_name from dba_tablespaces where retention like '%GUARANTEE')
/

col d format 999,999,999,999 head "Total Bytes"

select sum(bytes) d from dba_data_files  --表空间大小
where tablespace_name in
(select tablespace_name from dba_tablespaces where retention like '%GUARANTEE')
/


PROMPT
PROMPT  ############## UNDO SEGMENTS ############## 
PROMPT

col status head "Status"
col z format 999,999 head "Total Extents"
break on report
compute sum on report of z

select status, count(*) z  --表各状态空间区数量
from dba_undo_extents 
group by status
/

col z format 999,999 head "Undo Segments"  

select status, count(*) z   --表空间各状态回滚段数量
from dba_rollback_segs
group by status
/

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,
       nvl(sum(act), 0) "ACT BYTES",
       nvl(sum(unexp), 0) "UNEXP BYTES",
       nvl(sum(exp), 0) "EXP BYTES"
  from (select segment_name, nvl(sum(bytes), 0) act, 00 unexp, 00 exp
          from dba_undo_extents
         where status = 'ACTIVE'
         group by segment_name
        union
        select segment_name, 00 act, nvl(sum(bytes), 0) unexp, 00 exp
          from dba_undo_extents
         where status = 'UNEXPIRED'
         group by segment_name
        union
        select segment_name, 00 act, 00 unexp, nvl(sum(bytes), 0) exp
          from dba_undo_extents
         where status = 'EXPIRED'
         group by segment_name)
 group by segment_name
 order by 1
/
--我自己改写了一下
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

--ACTIVE          --事务尚未提交

--UNEXPIRED --事务已经提交 但提交时间尚未达到undo_RETENTION

--EXPIRED      --事务已经提交 但提交时间已经达到undo_RETENTION