Undo series learning-common scripts for Oracle DBAs

DBAs maintain Oracle and common undo scripts. Some of the previous articles also involve a lot of things. Here is a simple summary.

1) How to query the size of rollback segments used by a transaction in a database session?

sys@ORCL> select b.sid,b.username,a.xidusn,a.used_ublk  2         from v$transaction a,v$session b  3        where a.addr=b.taddr;       SID USERNAME                           XIDUSN  USED_UBLK---------- ------------------------------ ---------- ----------       147 HR                                      9          1       154 SCOTT                                   5          1

2) which user is using resources of the rollback segment?

select s.username,u.name   from v$transaction t,v$rollstat r,v$rollname u,v$session s where s.taddr=t.addr and       t.xidusn=r.usn and       r.usn=u.usn  order by s.usernameUSERNAME                       NAME------------------------------ ------------------------------HR                             _SYSSMU9$SCOTT                          _SYSSMU5$

3) displays the Undo tablespace statistical information-V $ undostat, which is automatically introduced and reported every 10 minutes.

select to_char(begin_time,'hh24:mi:ss') begin_time,       to_char(end_time,'hh24:mi:ss') end_time,       undoblks,       ssolderrcnt  from v$undostat;BEGIN_TI END_TIME   UNDOBLKS SSOLDERRCNT-------- -------- ---------- -----------12:02:41 12:04:33          0           011:52:41 12:02:41        109           011:42:41 11:52:41         35           011:32:41 11:42:41         52           011:22:41 11:32:41          2           0

4) display the statistics of online undo segments

select a.name,b.xacts,b.writes,b.extents  from v$rollname a,v$rollstat b where a.usn=b.usn;NAME                                XACTS     WRITES    EXTENTS------------------------------ ---------- ---------- ----------SYSTEM                                  0       5080          6_SYSSMU1$                               0     128452          4_SYSSMU2$                               0     131562          4_SYSSMU3$                               0     153310          4_SYSSMU4$                               0      95228          4_SYSSMU5$                               1      11954          9_SYSSMU6$                               0     211674         10_SYSSMU7$                               0     365956          7_SYSSMU8$                               0      68614         17_SYSSMU9$                               1      30516          5_SYSSMU10$                              0     219062         17