This content has been marked as final. Show 5 replies
1 person found this helpful
Any suggestion regarding this will be highly appreciated.increase size of UNDOTBS1
user8686720 wrote:You need increase UNDO_RETENTION parameter.Also use UNDO adviser and determine optimal size of undo tablespace.
At one of our production database we are observing ORA-0155 error quite frequently. Alert log reflects that this is caused by number of select and insert statements.
These statements are not always repeatable. Sometime we are also getting this ORA-30036: unable to extend segment by 4 in undo tablespace 'UNDOTBS1'.Set auto extensible on for undo tablespace.
Thanks to both of u Sb and Chinar for ur suggestion...
Sometimes it may not be good to set the UNDO tablespace to auto extensible.
This helps to prevent large running away SQL from happening.
It is not depend only in undo size but also
1) what is the max query length in you database?
select MAXQUERYID,MAXQUERYLEN from v$undostat;2) what is the undo retention period?
show paraemter undo1) either need tuning SQL query.
2) Increase undo retention ..
3) go for fixed UNDO.
i suggest you to read tom article as mentioned by SB. that is the best analysis document
*ORA-01555 "Snapshot too old" - Detailed Explanation [ID 40689.1]*
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
ROUND((d.undo_size / (to_number(f.value) *
g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size'