Here is a script to identify the active transaction are currently using rollback segments or undo segments. Conditions, log into the database as user SYS or SYSTEM or a user who has been in the grant DBA.
col o format a10 col u format a10 select osuser o, username u, sid, segment_name s, substr(sa.sql_text,1,200) txt from v$session s, v$transaction t, dba_rollback_segs r, v$sqlarea sa where s.taddr=t.addr and t.xidusn=r.segment_id(+) and s.sql_address=sa.address(+) And substr(sa.sql_text,1,200) is not null order by 3;
col name format a8 col username format a8 col osuser format a8 col start_time format a17 col status format a12 tti ‘Active transactions’ select username, osuser, t.start_time, r.name, t.used_ublk “ROLLB BLKS”, decode(t.space, ‘YES’, ‘SPACE TX’, decode(t.recursive, ‘YES’, ‘RECURSIVE TX’, decode(t.noundo, ‘YES’, ‘NO UNDO TX’, t.status) )) status from sys.v_$transaction t, sys.v_$rollname r, sys.v_$session s where t.xidusn = r.usn and t.ses_addr = s.saddrSubmit Article :- BlinkList + Blogmarks + Digg + Del.icio.us + Ekstreme Socializer + Feedmarker +Furl + Google Bookmarks + ma.gnolia + Netvouz + RawSugar + Reddit + Scuttle + Shadows +Simpy + Spurl + Technorati + Unalog + Wink