查UNDO统计信息语句：
SELECT TO_CHAR(BEGIN_TIME,'HH24:MI:SS') BEGIN_TIME,
TO_CHAR(END_TIME,'HH24:MI:SS') END_TIME,
UNDOBLKS
FROM V$UNDOSTAT;
-------------------------------------------结果--------------------------------------------------------------
begin_time end_time undoblocks
9:48:30        9:53:07        76293
9:38:30        9:48:30        143670
9:28:30        9:38:30        130921
9:18:30        9:28:30        122500
9:08:30        9:18:30        149030
8:58:30        9:08:30        144586
8:48:30        8:58:30        108095
8:38:30        8:48:30        63533
8:28:30        8:38:30        72819
8:18:30        8:28:30        23321
8:08:30        8:18:30        2026
7:58:30        8:08:30        15480
7:48:30        7:58:30        91111
7:38:30        7:48:30        15810
7:28:30        7:38:30        56922
7:18:30        7:28:30        30449
7:08:30        7:18:30        6096
6:58:30        7:08:30        15022
6:48:30        6:58:30        91904
6:38:30        6:48:30        38794
6:28:30        6:38:30        111884
----------------------------------------------------------------------------------------------------------
查UNDO表空间的使用情况：
select
  a.a1 表空间名,
  substr(b.b2/1024/1024/1024,1,5) 表空间大小G,
  substr(a.a2/1024/1024/1024,1,5) 剩余表空间G,
  substr((b.b2-a.a2)/1024/1024/1024,1,5) 实际使用表空间G,
  substr((b.b2-a.a2)/b.b2*100,1,5) 利用率,
  c.c2 类型,
  c.c3 区管理方式
  from
  (select tablespace_name a1,sum(Nvl(bytes,0)) a2 from dba_free_space group by tablespace_name) a,
  (select tablespace_name b1,sum(bytes) b2 from dba_data_files group by tablespace_name) b,
  (select tablespace_name c1,contents c2,extent_management c3 from dba_tablespaces) c
  where a.a1=b.b1 and b.b1=c.c1
  order by c.c2 desc
-------------------------------------------------结果--------------------------------------------------------------------
表空间名        表空间大小(G)        剩余(G)        使用        利用率        表空间类型        管理方式
UNDOTBS1        17.24        1.124        16.12        93.48        UNDO        LOCAL
----------------------------------------------------------------------------------------------------------------------------
建议UNDO峰值：
select ur undo_retention,dbs db_block_size, ((ur * (ups * dbs)) + (dbs * 24)) / 1024 / 1024 as "M_bytes"
from (select value as ur from v$parameter where name = 'undo_retention'),
       (select (undoblks / ((end_time - begin_time) * 86400)) ups from v$undostat where undoblks in (select max(undoblks) from  v$undostat ) ),
       (select value as dbs from v$parameter where name = 'db_block_size');
----------------------------------------------结果------------------------------------------------------------------
undo_retention        db_block_size        M_bytes
10800                              8192                   33,376.96875

Related posts:

1. 表空间使用状况查询
2. 用sql截取字符串