Gilson,
I'm not so sure it will change the size of the tablespace for you. As I
understand it, it's up to you to size the undo tablepspace to a sufficient
size. This size will depend on what the UNDO_RETENTION is set to and the
rate at which you are generating undo and the number of transactions,
Cheers,
Barry
Barry Scott
M&G Technical Services
Tel: 020 7548 3110
eMail: barry.[Email address protected]
|---------+--------------------------------------------------------->
| | "Gilson Sirvas " |
| | <oracledba-ezmlmshield-x82474822.[Email Address Removed] | azyDBA.com> |
| | |
| | 12/09/2004 22:11 |
| | |
|---------+--------------------------------------------------------->
>------------------------------------------------------------------------------------------------|
| |
| To: "LazyDBA Discussion" <[Email address protected] |
| cc: (bcc: Barry Scott/LON/M&G) |
| Subject: Re: ORA-01555 error |
>------------------------------------------------------------------------------------------------|
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
--------
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
Message ID - T6c011bea7fc0a8fa83404
The information contained in this message may be CONFIDENTIAL and is intended for the addressee only. Any unauthorised use, dissemination of the information, or copying of this message is prohibited. If you are not the addressee, please notify the sender immediately by return e-mail and delete this message. Although this e-mail and any attachments are believed to be free of any virus, or other defect which might affect any computer or system into which they are received and opened, it is the responsibility of the recipient to ensure that they are virus free and no responsibility is accepted by M&G for any loss or damage from receipt or use thereof.
Please note that all e-mail messages are subject to interception for lawful business purposes.
Oracle LazyDBA home page