Undo – Rollback Segment usage

select substr(a.os_user_name, 1, 8) "OS User",
 substr(a.oracle_username, 1, 8) "DB User",
 substr(b.owner, 1, 8) "Schema",
 substr(b.object_name, 1, 20) "Object Name",
 substr(b.object_type, 1, 10) "Type",
 c.segment_id "RBS#",
 to_number(substr(d.used_urec, 1, 12)) "# of Records"
 from v$locked_object a,
 dba_objects b,
 dba_rollback_segs c,
 v$transaction d,
 v$session e
 where a.object_id = b.object_id
 and a.xidusn = c.segment_id
 and a.xidusn = d.xidusn
 and a.xidslot = d.xidslot
 and d.addr = e.taddr
 order by 7, 6


SELECT s.username,
 FROM v$transaction t, v$session s, v$rollstat r, dba_rollback_segs rs
 WHERE s.saddr = t.ses_addr
 AND t.xidusn = r.usn
 AND rs.segment_id = t.xidusn
 ORDER BY t.used_ublk DESC

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: