This content has been marked as final. Show 5 replies
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.
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'