Hi

You can modify your parameter using the next command

alter system set undo_retention = 500 scope=memory; (For example)

SCOPE parameter means that you are going to change this parameter in a temporary way if you want to make it permanent use scope=spfile, but, that means that you have to restart your instance. if you want to make the change permanent you might use scope=both.

Be careful wen you change this parameter, it will affect the size of the undo tablespace.

Hope it helps you.

Gilson

----- Original Message -----
From: "Will Mayall " <oracledba-ezmlmshield-x67182855.[Email address protected]
Date: Sat, 11 Sep 2004 19:56:06 -0700
To: "LazyDBA Discussion" <[Email address protected]
Subject: Re: ORA-01555 error

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
>




--------
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


--
___________________________________________________________
Sign-up for Ads Free at Mail.com
http://promo.mail.com/adsfreejump.htm


Oracle LazyDBA home page