37 36 SCOTT _SYSSMU12_2144756092 1 1 11 sqlplus@rh
$ 6 (TNS V1-
V3)
Elapsed: 00:00:00.20
10:45:01 SCOTT@ prod>insert into t1 select * from t1 where rownum <20000;
19999 rows created.
Elapsed: 00:00:00.07
10:44:52 SYS@ prod>select s.sid,s.serial#,s.username,u.segment_name,count(u.extent_id) "Extent count",
2 t.used_ublk,t.used_urec,s.program
3 from v$session s,v$transaction t,dba_undo_extents u
4 where s.taddr=t.addr and u.segment_name like '_SYSSMU'||t.xidusn||'_%$' and u.status='ACTIVE'
5 GROUP BY s.sid,s.serial#,s.username,u.segment_name,t.used_ublk,t.used_urec,s.program
6* order by t.used_ublk desc,t.used_urec desc,s.sid,s.serial#,s.username,s.program
SID SERIAL# USERNAME SEGMENT_NAME Extent count USED_UBLK USED_UREC PROGRAM
---------- ---------- ---------- -------------------- ------------ ---------- ---------- ----------
37 36 SCOTT _SYSSMU16_2726800344 2 7 109 sqlplus@rh
$ 6 (TNS V1-
V3)
Elapsed: 00:00:00.01
10:45:13 SYS@ prod>
10:48:16 SYS@ prod>select a.usn,a.name,b.xacts,b.extents,b.status from v$rollname a,v$rollstat b
10:49:01 2 where a.usn=b.usn;
USN NAME XACTS EXTENTS STATUS
---------- ------------------------------ ---------- ---------- ---------------
0 SYSTEM 0 6 ONLINE
11 _SYSSMU11_2517864848$ 0 2 ONLINE
12 _SYSSMU12_2144756092$ 0 2 ONLINE
13 _SYSSMU13_527038519$ 0 3 ONLINE
14 _SYSSMU14_2951869305$ 0 2 ONLINE
15 _SYSSMU15_2206823906$ 0 2 ONLINE
16 _SYSSMU16_2726800344$ 1 2 ONLINE
17 _SYSSMU17_2098084560$ 0 2 ONLINE
10:50:45 SYS@ prod>select SEGMENT_NAME,TABLESPACE_NAME,EXTENT_ID,STATUS from dba_undo_extents
SEGMENT_NAME TABLESPACE_NAME EXTENT_ID STATUS
------------------------- ------------------------------ ---------- ---------
_SYSSMU17_2098084560$ UNDOTBS1 0 UNEXPIRED
_SYSSMU17_2098084560$ UNDOTBS1 1 UNEXPIRED
_SYSSMU16_2726800344$ UNDOTBS1 0 ACTIVE
_SYSSMU16_2726800344$ UNDOTBS1 1 ACTIVE
_SYSSMU15_2206823906$ UNDOTBS1 0 UNEXPIRED
_SYSSMU15_2206823906$ UNDOTBS1 1 UNEXPIRED
_SYSSMU14_2951869305$ UNDOTBS1 0 UNEXPIRED
_SYSSMU14_2951869305$ UNDOTBS1 1 UNEXPIRED
_SYSSMU13_527038519$ UNDOTBS1 0 UNEXPIRED
_SYSSMU13_527038519$ UNDOTBS1 1 UNEXPIRED
_SYSSMU13_527038519$ UNDOTBS1 2 UNEXPIRED
_SYSSMU12_2144756092$ UNDOTBS1 0 UNEXPIRED
_SYSSMU12_2144756092$ UNDOTBS1 1 UNEXPIRED
_SYSSMU11_2517864848$ UNDOTBS1 0 UNEXPIRED
_SYSSMU11_2517864848$ UNDOTBS1 1 UNEXPIRED
_SYSSMU30_1737877121$ UNDOTBS2 0 EXPIRED
_SYSSMU30_1737877121$ UNDOTBS2 1 UNEXPIRED
SEGMENT_NAME TABLESPACE_NAME EXTENT_ID STATUS
------------------------- ------------------------------ ---------- ---------
_SYSSMU30_1737877121$ UNDOTBS2 2 EXPIRED
_SYSSMU29_2754652023$ UNDOTBS2 0 EXPIRED
_SYSSMU29_2754652023$ UNDOTBS2 1 EXPIRED
_SYSSMU29_2754652023$ UNDOTBS2 2 EXPIRED
_SYSSMU29_2754652023$ UNDOTBS2 3 UNEXPIRED
_SYSSMU28_707429450$ UNDOTBS2 0 UNEXPIRED
_SYSSMU28_707429450$ UNDOTBS2 1 EXPIRED
_SYSSMU28_707429450$ UNDOTBS2 2 EXPIRED
_SYSSMU27_3269963619$ UNDOTBS2 0 EXPIRED
_SYSSMU27_3269963619$ UNDOTBS2 1 EXPIRED
_SYSSMU27_3269963619$ UNDOTBS2 2 EXPIRED
_SYSSMU27_3269963619$ UNDOTBS2 3 UNEXPIRED
_SYSSMU27_3269963619$ UNDOTBS2 4 EXPIRED
_SYSSMU27_3269963619$ UNDOTBS2 5 EXPIRED
_SYSSMU26_2968904537$ UNDOTBS2 0 EXPIRED
_SYSSMU26_2968904537$ UNDOTBS2 1 EXPIRED
_SYSSMU26_2968904537$ UNDOTBS2 2 EXPIRED
SEGMENT_NAME TABLESPACE_NAME EXTENT_ID STATUS
------------------------- ------------------------------ ---------- ---------
_SYSSMU26_2968904537$ UNDOTBS2 3 EXPIRED
_SYSSMU26_2968904537$ UNDOTBS2 4 UNEXPIRED
_SYSSMU26_2968904537$ UNDOTBS2 5 EXPIRED
_SYSSMU25_2810228709$ UNDOTBS2 0 EXPIRED
_SYSSMU25_2810228709$ UNDOTBS2 1 EXPIRED
_SYSSMU25_2810228709$ UNDOTBS2 2 EXPIRED
_SYSSMU25_2810228709$ UNDOTBS2 3 EXPIRED
_SYSSMU25_2810228709$ UNDOTBS2 4 EXPIRED
_SYSSMU25_2810228709$ UNDOTBS2 5 EXPIRED
_SYSSMU25_2810228709$ UNDOTBS2 6 EXPIRED
_SYSSMU25_2810228709$ UNDOTBS2 7 EXPIRED
_SYSSMU25_2810228709$ UNDOTBS2 8 EXPIRED
_SYSSMU25_2810228709$ UNDOTBS2 9 EXPIRED
_SYSSMU25_2810228709$ UNDOTBS2 10 EXPIRED
_SYSSMU25_2810228709$ UNDOTBS2 11 EXPIRED
_SYSSMU25_2810228709$ UNDOTBS2 12 EXPIRED
_SYSSMU25_2810228709$ UNDOTBS2 13 EXPIRED
SEGMENT_NAME TABLESPACE_NAME EXTENT_ID STATUS
------------------------- ------------------------------ ---------- ---------
_SYSSMU25_2810228709$ UNDOTBS2 14 EXPIRED
_SYSSMU25_2810228709$ UNDOTBS2 15 EXPIRED
_SYSSMU25_2810228709$ UNDOTBS2 16 EXPIRED
_SYSSMU25_2810228709$ UNDOTBS2 17 EXPIRED
_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),
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_size as DBS
10:59:51 7 FROM dba_tablespaces
11:00:01 8 where tablespace_name=(select upper(value) from v$parameter where));
BYTES
----------
87293952
Elapsed: 00:00:00.07
11:00:45 SYS@ prod>
11:00:45 SYS@ prod>select (UR * (UPS * DBS))/1024/1024 AS "BYTES"
11:01:42 2 from (select value AS UR from v$parameter where),
11:01:42 3 (select undoblks/((end_time-begin_time)*900) as UPS
11:01:42 4 FROM v$undostat
11:01:42 5 where undoblks=(select max(undoblks) from v$undostat)),
11:01:42 6 (select block_size as DBS
11:01:42 7 FROM dba_tablespaces
11:01:42 8 where tablespace_name=(select upper(value) from v$parameter where));
BYTES
----------
83.25
Elapsed: 00:00:00.10
11:01:44 SYS@ prod>
以上部分内容参考了《Oracle DBA实战攻略》书中的内容，这里表示感谢！