ORA-01555: snapshot too old

From: Guang Mei <gmei_at_proteome.com>
Date: Mon, 16 Jul 2001 16:35:23 -0400
Message-ID: <kcI47.30$B6.4620@news.shore.net>

Hi:

I know this question has been asked and answered many times. But I am still not 100% clear.

We have a cron job every night to exp some schemas(oracle 8.05 on Sun). We got the following error during last night's exp for the first time:

EXP-00008: ORACLE error 1555 encountered

ORA-01555: snapshot too old: rollback segment number 2 with name "R02" too small

EXP-00000: Export terminated unsuccessfully

SQL> select SEGMENT_NAME,INITIAL_EXTENT,NEXT_EXTENT,

2 MIN_EXTENTS,MAX_EXTENTS from dba_rollback_segs;

SEGMENT_NAME INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS

-------------- ----------- -----------

MAX_EXTENTS

SYSTEM 51200 51200 2 121

R01 2097152 2097152 20

500

R02 2097152 2097152 20

500

SEGMENT_NAME INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS

-------------- ----------- -----------

MAX_EXTENTS

R03 2097152 2097152 20

500

R04 2097152 2097152 20

500

So in theory, each transaction could use up to 1024M.

SQL> select name,WRAPS ,EXTENDS,HWMSIZE,gets,waits

2 from v$rollstat, v$rollname

3 where v$rollstat.usn =v$rollname.usn;

NAME WRAPS EXTENDS HWMSIZE GETS

---------- ---------- ---------- ----------

WAITS

SYSTEM 0 0 1277952 14757 0

R01 297 16 75530240 3435839

R02 366 58 163729408 3635643

NAME WRAPS EXTENDS HWMSIZE GETS

---------- ---------- ---------- ----------

WAITS

R03 352 57 161636352 4369141

R04 310 21 86065152 3626204

According to doc, the error is due to smaller/fewer rollback segments. But why R02 HWMSIZE is only 164M ( 163729408 ) when it could use 1024M? Or were there too many other transactions going on (therefore overwrite data in rollback segments) during the exp that cause this error?

What change(s) could I make to avoid this error? Add more rollback segments or set larger MAX_EXTENTS values? Assuming I don't have extra disk space to increase RBS tablespace.

TIA Guang Received on Mon Jul 16 2001 - 15:35:23 CDT