Anyone can help me on determine the size of rollback segment?
My requirement is to update one column in table (with 1,500K records) within short period. For that I had created a private rollback segment, and using PL/SQL script i updated the table.
The PL/SQL Script
set timing on
set serveroutput on
lv_date1 := '01.jan.00';
lv_date2 := lv_date1 + 15;
update ordermaster set status ='N'
where orderdte between lv_date1 and lv_date2;
if lv_date1 >= '1-jun-03' then
lv_date1 := lv_date2;
The Orderdte column is an indexed field. Before run this script I disabled the trigger associated with this table.
I had tested this script under rollback segment with different parameters, still I am not able to conclude how to determine the size of rollback segment. For testing, I had taken 5 months record (from 1/jan/03 to 31/may/03) and did for several times.
INITIAL & NEXT size of rollback segment is 1024K in all cases.
Size of Rollback - Date Range in Where Clause - Execution Time
MIN10 , MAX80 - 15days - 510 seconds
MIN5, MAX80 - 15days - 784 seconds
MIN30, MAX 160 - 15days - 461 seconds
MIN30, MAX 160 - 10 days - 316 seconds
MIN60, MAX 240 - 15days - 221 seconds
MIN100, MAX 320 - 30days - 290 seconds
MIN100, MAX 320 - 15days - 205 seconds