/* ************************************************************** */
/* Create the script to take the rollbacks offline                */
/*   - only those that are not in use                             */
/* ************************************************************** */

set echo off;
set feedback off;
set heading off;

/* ****************************************** */
/* 1. First shrink the rollback segments      */
/* ****************************************** */

select 'alter rollback segment '||segment_name|| ' shrink;' " "
from dba_rollback_segs
where status = 'ONLINE'
and segment_name not in (select r.name
from v$transaction t, v$rollname r, v$session s
where t.addr = s.taddr
and t.xidusn=r.usn);

spool shrink_rollbacks.out;
/
spool off; 

@shrink_rollbacks.out
