_SYSSMU25_2810228709$ UNDOTBS2 18 EXPIRED
_SYSSMU25_2810228709$ UNDOTBS2 19 EXPIRED
_SYSSMU25_2810228709$ UNDOTBS2 20 EXPIRED
_SYSSMU25_2810228709$ UNDOTBS2 21 EXPIRED
_SYSSMU25_2810228709$ UNDOTBS2 22 EXPIRED
_SYSSMU25_2810228709$ UNDOTBS2 23 EXPIRED
_SYSSMU25_2810228709$ UNDOTBS2 24 EXPIRED
_SYSSMU25_2810228709$ UNDOTBS2 25 EXPIRED
_SYSSMU25_2810228709$ UNDOTBS2 26 EXPIRED
_SYSSMU25_2810228709$ UNDOTBS2 27 EXPIRED
_SYSSMU25_2810228709$ UNDOTBS2 28 EXPIRED
_SYSSMU25_2810228709$ UNDOTBS2 29 EXPIRED
_SYSSMU25_2810228709$ UNDOTBS2 30 EXPIRED
SEGMENT_NAME TABLESPACE_NAME EXTENT_ID STATUS
------------------------- ------------------------------ ---------- ---------
_SYSSMU25_2810228709$ UNDOTBS2 31 EXPIRED
_SYSSMU25_2810228709$ UNDOTBS2 32 EXPIRED
_SYSSMU25_2810228709$ UNDOTBS2 33 EXPIRED
_SYSSMU25_2810228709$ UNDOTBS2 34 EXPIRED
_SYSSMU25_2810228709$ UNDOTBS2 35 UNEXPIRED
_SYSSMU24_386518199$ UNDOTBS2 0 EXPIRED
_SYSSMU24_386518199$ UNDOTBS2 1 UNEXPIRED
_SYSSMU24_386518199$ UNDOTBS2 2 EXPIRED
_SYSSMU23_4084707454$ UNDOTBS2 0 EXPIRED
_SYSSMU23_4084707454$ UNDOTBS2 1 UNEXPIRED
_SYSSMU23_4084707454$ UNDOTBS2 2 EXPIRED
_SYSSMU22_3375463809$ UNDOTBS2 0 UNEXPIRED
_SYSSMU22_3375463809$ UNDOTBS2 1 UNEXPIRED
_SYSSMU22_3375463809$ UNDOTBS2 2 EXPIRED
_SYSSMU22_3375463809$ UNDOTBS2 3 EXPIRED
_SYSSMU21_2312338076$ UNDOTBS2 0 EXPIRED
_SYSSMU21_2312338076$ UNDOTBS2 1 EXPIRED
SEGMENT_NAME TABLESPACE_NAME EXTENT_ID STATUS
------------------------- ------------------------------ ---------- ---------
_SYSSMU21_2312338076$ UNDOTBS2 2 UNEXPIRED
86 rows selected.
Elapsed: 00:00:00.06
10:52:33 SYS@ prod>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
Elapsed: 00:00:00.02
10:53:03 SYS@ prod>select BEGIN_TIME,END_TIME,UNDOTSN,UNDOBLKS,TUNED_UNDORETENTION from v$undostat;
BEGIN_TIME END_TIME UNDOTSN UNDOBLKS TUNED_UNDORETENTION
------------------- ------------------- ---------- ---------- -------------------
2014-07-01 10:52:51 2014-07-01 10:53:08 2 0 921
2014-07-01 10:42:51 2014-07-01 10:52:51 2 74 921
2014-07-01 10:32:51 2014-07-01 10:42:51 2 43 1260
估算undo tablespace 的大小：
UndoTablesapce = UR * (UPS * DBS)
UR: undo_retention
UPS:在业务高峰期每秒产生的undo blocks的数量
DBS:undo tablespace的数据库的大小
10:53:08 SYS@ prod>select (UR * (UPS * DBS)) AS "BYTES"
10:56:12 2 from (select value AS UR from v$parameter where name='undo_retention'),
10:57:04 3 (select undoblks/((end_time-begin_time)*900) as UPS
10:58:39 4 FROM v$undostat
10:58:50 5 where undoblks=(select max(undoblks) from v$undostat)),
10:59:29 6 (select block_si