REM show all rollback segments status

set heading on
set linesize 300
set echo off
set timing off
set feedback on

column "Instance" format a14
column "Rollback segemnt" format a16
column "Status" format a10
column "Exist extents" format 999,999,999
column "Current extent" format 999,999,99
column "Current block" format 999,999,999
column "Transactions" format 999,999
column "Allocated size(Kb)" format 999,999,999,999
column "Write size(Kb)" format 999,999,999
column "OPTIMAL SIZE(Kb)" format 999,999
column "HIGH WATER SIZE(Kb)" format 999,999
column "Ave shrink size(Kb)" format 999,999
column "Ave active size(Kb)" format 999,999
column "N_of_t Shrinks" format 999,999
column "N_of_t extended" format 999,999
column "N_of_t wrapped" format 9999,999
column "Header gets" format 9999,999
column "Header waits" format 9999,999

spool rollback_segment.lst

SELECT substr(c.inst_name,1,14) "Instance",
a.segment_name "Rollback segemnt",
b.status "Status",
b.EXTENTS "Exist extents",
b.curext "Current extent",
b.curblk "Current block",
b.XACTS "Transactions",
b.RSSIZE/1024 "Allocated size(Kb)",
b.WRITES/1024 "Write size(Kb)",
b.OPTSIZE/1024 "OPTIMAL SIZE(Kb)",
b.HWMSIZE/1024 "HIGH WATER SIZE(Kb)",
b.aveshrink/1024 "Ave shrink size(Kb)",
b.aveactive/1024 "Ave active size(Kb)",
b.SHRINKS "N_of_t Shrinks",
b.EXTENDS "N_of_t extended",
b.wraps "N_of_t wrapped",
b.gets "Header gets",
b.waits "Header waits"
FROM dba_rollback_segs a,v$rollstat b,v$active_instances c
where a.segment_id=b.usn
and a.instance_num=c.inst_number(+)
order by 2;


column "Rollback_seg_nm" format a16
column "Taplespace_nm" format a16
column "Data file" format a20
column "Init_extent(Kb)" format 999,999
column "Next_extent(Kb)" format 999,999
column "Min_extents" format 999,999
column "Max_extents" format 99999,999,999
column "Instance_num" format a15

select decode(a.instance_num,1,'TA-BSCS2',
2,'TA-BSCS1') "Instance",
a.segment_name "Rollback_seg_nm",
a.segment_id "Rollback_seg_id",
a.tablespace_name "Taplespace_nm",
b.file_name "Data file",
a.status "Status",
a.initial_extent/1024 "Init_extent(Kb)",
a.next_extent/1024 "Next_extent(Kb)",
a.min_extents "Min_extents",
a.max_extents "Max_extents",
a.pct_increase "Pct_increase"
from dba_rollback_segs a,dba_data_files b
where a.file_id=b.file_id
order by 1,4,2;

spool off
/