While executing a procedure, I am getting the 'snapshot too old...' error --
SQL> exec sms.computeloadterminaltm
begin sms.computeloadterminaltm; end;
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 7 with name "RBS4" too small
ORA-06512: at "SMS.COMPUTELOADTERMINALTM", line 24
I don't think though that increasing the rollback size will help because there is plenty of space in the tablespace for the rollback to grow but the high-water mark shows that it's not extending beyond initial 2 extents (mening it does not need more space for rollback)
The procedure involves a long group-by query that sends results into a table (insert into...)
Any idea why this "snapshot..." error when there is room for rollback to grow ?
you better set initial extents to at least 20. The size of tablespace is not the problem here, the problem is the extent in RBS4 which a transaction is using has been overwritten by another transaction while you were querying that extent.
When a rollback segment grows, it does this circularly, if the next extent which it wants to extend to is occupied it must *spawn* another extent, if then the occupied extents is freed it will be eligible again to be overwritten by another transactions, now if a long query started just before the extent was freed and reaches the freed extent it will find an inconsistency because that extent would be overwritten by other transactions and causing SNAPSHOT TOO OLD error. To avoid this you need high number of initial extents and bigger size extents.