WILL>>Try running:
SELECT segment_name, tablespace_name, bytes, blocks, extents
FROM sys.dba_segments where tablespace_name like 'UN%';

Have you tried to autoextent the UNDO tablespace?

alter DATABASE DATAFILE '/uXX/oradata/$ORACLE_SID/UNDOTBS1.dbf'
AUTOEXTEND ON NEXT 10M;

The UNDO tablespace parameters may be in your init<$ORACLE_SID>.ora
file.

Check $ORACLE_HOME/admin/$ORACLE_SID/pfile directory.

###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_management=AUTO
undo_retention=10900
undo_tablespace=UNDOTBS1

If you modify the init.ora you will have to reboot the database.


Shibashish wrote:
> 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
>
>
> --------
> website: http://www.LazyDBA.com
> Please don't reply to RTFM questions
> Oracle documentation is here: http://tahiti.oracle.com
> To unsubscribe: see http://www.lazydba.com/unsubscribe.html
> To subscribe: see http://www.lazydba.com
> By using this list you agree to these terms:http://www.lazydba.com/legal.html
>



Oracle LazyDBA home page