SELECT
seg.tablespace_name "Tablespace Name",
ts.bytes/1024/1024 "TS Size(MB)",  
ue.status "UNDO Status",
count(*)  "Used Extents",  
round(sum(ue.bytes)/1024/1024, 2) "Used Size(MB)",  
round(sum(ue.bytes)/ts.bytes*100, 2) "Used Rate(%)"
FROM dba_segments seg, DBA_UNDO_EXTENTS  ue,  
(SELECT tablespace_name, sum(bytes) bytes    
   FROM dba_data_files GROUP BY tablespace_name) ts
    WHERE ue.segment_NAME=seg.segment_NAME and seg.tablespace_name=ts.tablespace_name
    GROUP BY seg.tablespace_name, ts.bytes, ue.status ORDER BY seg.tablespace_name;
    
  


select t2.tablespace_name ,t1.status, sum(t2.BYTES)/1024/1024  from DBA_ROLLBACK_SEGS t1  , dba_segments t2  where t1.segment_name=t2.segment_name group by  t2.tablespace_name, t1.status;