Feng
Try this.
ALTER ROLLBACK SEGMENT <> SHRINK TO size; -IF OPTIMAL SIZE is defined , or TO MINEXTENT SIZE if optimal size is not defined.....Additionally SMON will try once every 12 hours to shrink all rollback segments that have grown
over the optimal
To shrink all rollback segments do the following
SET PAGES 0
TRIMS ON
ECHO OFF
VERIFY OFF
FEEDBACK OFF
TTITLE OFF
CLEAR COLUMN
SPOOL shrink_rbs.tmp
PROMPT SET ECHO ON
PROMPT SPOOL shrink_rbs.log
PROMPT
SELECT 'ALTER ROLLBACK SEGMENT ' || segment_name
|| ' SHRINK;'
FROM dba_rollback_segs
WHERE status = 'ONLINE'
/
PROMPT ALTER TABLESPACE rbs COALESCE
/
PROMPT SPOOL OFF
PROMPT SET ECHO OFF
SPOOL OFF
CLEAR COLUMN
SET PAGES 24
TRIMS ON
ECHO OFF
VERIFY ON
FEEDBACK ON
@shrink_rbs.tmp
PROMPT
PROMPT ******************************
PROMPT
PROMPT Output saved at shrink_rbs.log
PROMPT
Hope this help
Arsene Lupain
The lie takes the elevator, the truth takes the staircase but ends up catching up with the lie.