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;
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