Oracle 常用sql整理

1. 查看当前正在只用的undo段

select s.sid, s.serial#, s.username, r.name, t.STATUS, t.START_TIME, t.USED_UBLK, t.cr_get, t.phy_io, t.used_ublk, t.noundo ,s.program

    from v$rollname r, v$transaction t, v$session s

        where t.addr=s.taddr and t.xidusn=r.usn

              order by t.cr_get,phy_io;

2. 查看回滚段争用,ratio大于2表示该回滚段争用

select a.name, b.gets, b.waits, (b.waits/b.gets)*100 ratio

       from v$rollname a, v$rollstat b

            where a.usn=b.usn;