Saturday, June 4, 2011

How to find out objectwise undo usage ???

set lines 132 pages 2300
col object_name format a30
col username format a13
col name format a14
col EXTENTS format 9999999
col SID_SERIAL format a15
col sql_text format a40 wrap
col undo_MB format 99,999.99
select object_name,sum(undo_mb) from (select  s.sid||','||s.serial# SID_SERIAL, s.username, round(t.used_ublk * TO_NUMBER(x.value)/1024/1024,2) undo_MB, sq.sql_text ,do.object_name
        from    v$transaction   t
               ,v$session       s
               ,v$rollname      rn
               ,v$rollstat      rs
               ,v$locked_object lo
               ,dba_objects     do
               ,v$sql           sq
               ,v$parameter   x
        where  t.addr        = s.taddr
        and    t.xidusn      = rn.usn
        and    rn.usn        = rs.usn
        and    t.xidusn      = lo.xidusn(+)
        and    do.object_id  = lo.object_id
        and    s.sql_id      = sq.sql_id(+)
        AND    x.name  = 'db_block_size'
        order by used_ublk desc) group by object_name order by 2 desc;

No comments:

Post a Comment