查看undo tablespace 使用情况：
10:43:29 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 _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$ U