(1)查看undo表空间undotbs1的属性:
SQL> select b.tablespace_name,AUTOEXTENSIBLE,RETENTION FROM dba_tablespaces a,dba_data_files b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME and b.TABLESPACE_NAME='UNDOTBS1';
TABLESPACE_NAME AUTOEXTENSIBLE RETENTION
------------------------------ -------------- -----------
UNDOTBS1 YES NOGUARANTEE
(2)查看各个回退段的使用信息:
SQL>select a.name,b.extents,b.rssize,b.writes,b.xacts,b.wraps
FROM v$rollname a,v$rollstat b where a.usn=b.usn;
NAME EXTENTS RSSIZE WRITES XACTS WRAPS
------------------------------ ---------- ---------- ---------- ---------- ----------
SYSTEM 6 385024 714270 0 12
_SYSSMU1$ 6 4317184 2543589636 0 12059
_SYSSMU2$ 3 1171456 2901541960 0 11799
_SYSSMU3$ 3 1171456 2921545816 0 11645
_SYSSMU4$ 3 1171456 1803246508 0 11570
_SYSSMU5$ 5 3268608 2371130580 0 11376
_SYSSMU6$ 3 1171456 3369052188 0 12099
_SYSSMU7$ 3 1171456 3165657952 0 12063
_SYSSMU8$ 3 1171456 2901164772 0 11931
(3)确定哪些用户正在使用undo段:
SQL> select a.username,b.name,c.used_ublk from v$session a,v$rollname b,v$transaction c
where a.saddr=c.ses_addr and b.usn=c.xidusn;
USERNAME NAME USED_UBLK
---------- ----------------------- ----------
NDMC _SYSSMU1_1255220753$ 1
NDMC _SYSSMU5_1255220754$ 1
(4)每秒生成的UNDO量,可以通过如下SQL得出:
SQL> SELECT (SUM(undoblks))/ SUM((end_time - begin_time) * 86400) FROM v$undostat;
(SUM(UNDOBLKS))/SUM((END_TIME-
------------------------------
0.842164720604124
或者可以看下AWR报告中的Undo Statistics部分。
(5)当前undo表空间使用状态:
SQL> SELECT DISTINCT STATUS,SUM(BYTES),COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS;
STATUS SUM(BYTES) COUNT(*)
--------- ---------- ----------
UNEXPIRED 11403264 24
EXPIRED 152502272 355
(6)查看活动事务v$transaction
SQL> SELECT A.SID, A.USERNAME, B.XIDUSN, B.USED_UREC, B.USED_UBLK
FROM V$SESSION A, V$TRANSACTION B WHERE A.SADDR=B.SES_ADDR;
SID USERNAME XIDUSN USED_UREC USED_UBLK
---------- ------------ ---------- ---------- ----------
407 NDMC 15 3 1
SQL> SELECT XID AS "txn_id", XIDUSN AS "undo_seg", USED_UBLK "used_undo_blocks",
XIDSLOT AS "slot", XIDSQN AS "seq", STATUS AS "txn_status"
FROM V$TRANSACTION;
txn_id undo_seg used_undo_blocks slot seq txn_status
---------------- ---------- ---------------- ---------- --------- -------------
14001600733A0C00 20 1 22 801395 ACTIVE