1.查看oracle undo segment段的信息http://www.cndba.cn/hbhe0316/article/4961

SELECT T1.USN,
       T2.NAME,
       T1.STATUS,
       T1.LATCH,
       T1.EXTENTS,
       T1.WRAPS,
       T1.EXTENDS
  FROM V$ROLLSTAT T1, V$ROLLNAME T2
 WHERE T1.USN = T2.USN;

       USN NAME                                         STATUS        LATCH    EXTENTS      WRAPS    EXTENDS
---------- -------------------------------------------------------------------------------- ---------- ---------- ---------- ---------- ----------
     0 SYSTEM                                        ONLINE        0       7          0      0
     1 _SYSSMU1_1261223759$                                 ONLINE        1       3        215     61
     2 _SYSSMU2_27624015$                                    ONLINE        2       3        282        116
     3 _SYSSMU3_2421748942$                                 ONLINE        3       3        266        115
     4 _SYSSMU4_625702278$                                    ONLINE        4       4        237     75
     5 _SYSSMU5_2101348960$                                 ONLINE        5       3        255     87
     6 _SYSSMU6_813816332$                                    ONLINE        6       3        254     88
     7 _SYSSMU7_2329891355$                                 ONLINE        7       3        736        599
     8 _SYSSMU8_399776867$                                    ONLINE        8       4        325        145
     9 _SYSSMU9_1692468413$                                 ONLINE        9      15        306        123
    10 _SYSSMU10_930580995$                                 ONLINE           10       4        467        223

11 rows selected.

2.检查事务使用undo segment的情况http://www.cndba.cn/hbhe0316/article/4961http://www.cndba.cn/hbhe0316/article/4961

SELECT s.username,
       s.sid,
       pr.PID,
       s.OSUSER,
       s.MACHINE,
       s.PROGRAM,
       rs.segment_id,
       r.usn,
       rs.segment_name,      
       r.rssize/1024/1024,
       sq.sql_text
  FROM gv$transaction t, gv$session s, gv$rollstat r, dba_rollback_segs rs ,gv$sqltext  sq,gv$process pr
WHERE s.saddr = t.ses_addr
   AND t.xidusn = r.usn 
   AND rs.segment_id = t.xidusn
   AND s.sql_address=sq.address
   AND s.sql_hash_value = sq.hash_value
   AND s.PADDR=pr.ADDR
ORDER BY t.used_ublk DESC ,sq.PIECE;

版权声明：本文为博主原创文章，未经博主允许不得转载。

oracle,linux