rollback segment

SELECT owner,segment_name,partition_name, sum(bytes)

FROM dba_extents

WHERE tablespace_name='RBS'

GROUP BY owner,segment_name,partition_name

ORDER BY 4 DESC

alter rollback segment rbs13 shrink

--TABLESPACE SPACE CHECK (SPACE MANAGEMENT)

SELECT d.tablespace_name "Name", d.status "Status",

to_char((a.bytes / 1024 / 1024),'99,999,990.900') "Size (M)",

to_char(((a.bytes - decode(f.bytes, null, 0, f.bytes)) / 1024 / 1024),'99,999,990.900') "Used (M)",

to_char((a.bytes - decode(f.bytes, null, 0, f.bytes))*100/a.bytes,'99,99,990') "USED %"

FROM sys.dba_tablespaces d, sys.sm$ts_avail a, sys.sm$ts_free f

WHERE d.tablespace_name = a.tablespace_name

AND f.tablespace_name (+) = d.tablespace_name

ORDER by 5 desc

SELECT segs.SEGMENT_NAME "RB NAME ",sta.RSSIZE, p.spid "SYSTEM PID ",s.sid,s.serial#,

NVL (p.username, 'NO TRANSACTION') "OS USER", s.username , action, p.terminal, tablespace_name

FROM v$rollstat sta , dba_rollback_Segs segs, v$process p , v$session s ,v$lock l

WHERE l.sid = s.sid(+)

AND s.paddr = p.addr

AND TRUNC (l.id1(+)/65536) = sta.usn

AND l.type(+) = 'TX'

AND l.lmode(+) = 6

AND segs.segment_id=usn

ORDER BY sta.rssize desc

shrink

SELECT 'ALTER rollback segment '||segment_name||' shrink;'

FROM dba_segments WHERE tablespace_name!='SYSTEM' AND SEGMENT_TYPE LIKE 'R%';