I am geting the following error when i am executing a procedure...

ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 7 with name "_SYSSMU7$" too small

again when i execute another procedure...

ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 5 with name "_SYSSMU5$" too small

// *Cause: rollback records needed by a reader for consistent read are
// overwritten by other writers
// *Action: If in Automatic Undo Management mode, increase undo_retention
// setting. Otherwise, use larger rollback segments

1. How and where do i increase the undo_retention parameter ?? init.ora ?? or initSID.ora ?? (i dont have any such parameter in them)

2. Will creating new rollback segments help ?
3. Is it necessary that the rollback segment should be in the same tablespace ?
4. How do i decide on the size of the rollback segment ?
5. What are the typical values for initial, next, optimal sizes, minimum n maximim number ?
6. How do i check how much space in the rollback is being taken up by the procedure/function ?

I am using Oracle 9.2.0.1/RedHat2.1AS. Thanks.


SQL> SELECT segment_name, tablespace_name, status
FROM sys.dba_rollback_segs;
2
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
_SYSSMU1$ UNDOTBS1 ONLINE
_SYSSMU2$ UNDOTBS1 ONLINE
_SYSSMU3$ UNDOTBS1 ONLINE
_SYSSMU4$ UNDOTBS1 ONLINE
_SYSSMU5$ UNDOTBS1 ONLINE
_SYSSMU6$ UNDOTBS1 ONLINE
_SYSSMU7$ UNDOTBS1 ONLINE
_SYSSMU8$ UNDOTBS1 ONLINE
_SYSSMU9$ UNDOTBS1 ONLINE
_SYSSMU10$ UNDOTBS1 ONLINE

11 rows selected.


SQL> SELECT segment_name, tablespace_name, bytes, blocks, extents
FROM sys.dba_segments
WHERE segment_type = 'ROLLBACK';
2 3
SEGMENT_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME BYTES BLOCKS EXTENTS
------------------------------ ---------- ---------- ----------
SYSTEM
SYSTEM 393216 48 6

Oracle LazyDBA home page