undo表空间中常用的操作： （1）查看undo表空间undotbs1的属性：SQLgt; select b.tablespace_name,AUTOEXTENSIBLE,RETENTION f undo表空间中常用的操作：（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 AUT RETENTION---------------------- --- ----------------UNDOTBS1 NO NOGUARANTEE（2）查看各个回退段的使用信息：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;（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-BEGIN_TIME)*86400)--------------------------------------------------------------
7.97590055
或者可以看下AWR报告中的Undo Statistics部分。
（5）当前undo表空间使用状态:SQL> SELECT DISTINCT STATUS,SUM(BYTES),COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS;STATUS SUM(BYTES) COUNT(*)--------- ---------------- ----------UNEXPIRED 3225157632 5667 EXPIRED 1063518208 1588 ACTIVE 1048576 1（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