BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
We have fixed size undo tablspace of 2Gb with undo_retention period of 1 hour.
We are getting ORA-01555: snapshot too old: rollback segment number 51 with name "_SYSSMU51$" too small error during execution of purging process.
I want to know If there are expired blocks available and steal count for unexpired blocks is 0, then why we get ORA-01555 error.
According to below query output:
EXPIREDBLKS = 138616
UNXPSTEALCNT = 0
set line 2000
SELECT *
FROM dba_hist_UNDOSTAT
where ssolderrcnt <> 0
BEGIN_TIME END_TIME DBID INSTANCE_NUMBER SNAP_ID UNDOTSN UNDOBLKS TXNCOUNT MAXQUERYLEN MAXQUERYSQLID MAXCONCURRENCY UNXPSTEALCNT UNXPBLKRELCNT UNXPBLKREUCNT EXPSTEALCNT EXPBLKRELCNT EXPBLKREUCNT SSOLDERRCNT NOSPACEERRCNT ACTIVEBLKS UNEXPIREDBLKS EXPIREDBLKS TUNED_UNDORETENTION
--------------------- --------------------- ---- --------------- ------- ------- -------- -------- ----------- ------------- -------------- ------------ ------------- ------------- ----------- ------------ ------------ ----------- ------------- ---------- ------------- ----------- -------------------
6/21/2014 10:15:21 AM 6/21/2014 10:25:21 AM 8337 2 50429 8 17969 27294 15780 77kuu26yggxym 19 0 0 0 0 0 0 1 0 9904 1549952 138616 13262