|
Wie gebruikt welk rollbacksegment |
Dit script laat zien welke user welk rollback segment gebruikt.
create function whorbs(Nr in number default null)
return varchar2
is
begin
if (Nr is null) then
return(null);
elsif (Nr = 0) then
return (0);
elsif (Nr < 1024) then
return (to_char(trunc(Nr))||'b');
elsif (Nr < 1024*1024) then
return (to_char(trunc(Nr/1024))||'K');
elsif (Nr < 1024*1024*1024) then
return (to_char(trunc(Nr/1024/1024))||'M');
--elsif (Nr < 1024*1024*1024*1024) then
else
return (to_char(trunc(Nr/1024/1024/1024))||'G');
end if;
end;
/
set termout on
col usr format a15 heading "OraUser" trunc
col osu format a12 heading "OsUser" trunc
col nam format a10 heading "RBS" trunc
col txtim format a9 heading "TX Start"
col xac format 99 heading "Rbs|ActTX"
col ext format 999 heading "Rbs|Ext"
col sid format 999 heading "Sid"
col ser format 99999 heading "Ser#"
col siz format a5 heading "Rbs|Size"
prompt Info on which user is using which rollback segment.
select s.username usr, s.osuser osu,
s.sid sid, s.serial# ser, rn.name nam, rs.xacts xac, rs.extents ext,
whorbs(rs.rssize) siz
from v$session s, v$transaction t, v$rollname rn, v$rollstat rs
where s.taddr = t.addr
and t.xidusn = rn.usn
and t.xidusn = rs.usn
order by usr
/
drop function whorbs; |
|
|
