ORA-01555: snapshot too old: rollback segment number 24 with name "_SYSSMU24$" too small

From: arul kumar <arul76_2000_at_yahoo.com>
Date: Tue, 27 Jun 2006 12:36:47 -0700 (PDT)
Message-ID: <20060627193647.73666.qmail@web54603.mail.yahoo.com>

Dear DBAs,

I have this recent error in alert log coming from the stats update job. Though the SQL is given partly, not able to find the EXACT database object that had problem. Any clue?

..
....
Tue Jun 27 03:40:20 2006
ORA-01555 caused by SQL statement below (Query Duration=1151404820 sec, SCN: 0x0849.81c92aa1): Tue Jun 27 03:40:20 2006
SELECT /*+ PIV_SSF */
SYS_OP_MSR(MAX(A1.C154),MIN(A1.C154),SUM(VSIZE(A1.C154)),COUNT(DISTINCT A1.C154),MAX(A1.C153),MIN(A1.C153),SUM
(VSIZE(A1.C153)),COUNT(DISTINCT
A1.C153),MAX(A1.C152),MIN(A1.C152),SUM(VSIZE(A1.C152)),COUNT(DISTINCT A1.C152),MAX(A1.C151),MIN(A1.C
151),SUM(VSIZE(A1.C151)),COUNT(DISTINCT
A1.C151),MAX(A1.C150),MIN(A1.C150),SUM(VSIZE(A1.C150)),COUNT(DISTINCT A1.C150),MAX(A1.C149),
MIN(A1.C149),SUM(VSIZE(A1.C149)),COUNT(DISTINCT A1.C149),MAX(A1.C148),MIN(A1.C148),SUM(VSIZE(A1.C148)),COUNT(DISTINCT A1.C148),MAX(A
1
Tue Jun 27 03:40:21 2006
Errors in file
/u09/app/oracle/admin/XYZ/bdump/XYZ_j000_10657.trc: ORA-12012: error on auto execute of job 201 ORA-12801: error signaled in parallel query server P003
ORA-01555: snapshot too old: rollback segment number 24 with name "_SYSSMU24$" too small

ORA-06512: at "SYS.DBMS_STATS", line 10070
ORA-06512: at "SYS.DBMS_STATS", line 10564
ORA-06512: at "SYS.DBMS_STATS", line 10751
ORA-06512: at "SYS.DBMS_STATS", line 10805
ORA-06512: at "SYS.DBMS_STATS", line 10782
ORA-06512: at line 1

Tue Jun 27 03:41:19 2006
...
.....

cat /u09/app/oracle/admin/XYZ/bdump/XYZ_j000_10657.trc

Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
ORACLE_HOME = /u09/app/oracle/product/XYZ/9.2.0.5 System name: SunOS

Node name:      c6ktemuxs01
Release:        5.8
Version:        Generic_117350-26
Machine:        sun4u

Instance name: XYZ
Redo thread mounted by this instance: 1
Oracle process number: 44
Unix process pid: 10657, image: oracle_at_c6ktemuxs01 (J000)

• SESSION ID:(246.1239) 2006-06-25 12:10:32.526
• 2006-06-25 12:10:32.526 ORA-12012: error on auto execute of job 201 ORA-01555: snapshot too old: rollback segment number 20 with name "_SYSSMU20$" too small ORA-06512: at "SYS.DBMS_STATS", line 10070 ORA-06512: at "SYS.DBMS_STATS", line 10564 ORA-06512: at "SYS.DBMS_STATS", line 10751 ORA-06512: at "SYS.DBMS_STATS", line 10805 ORA-06512: at "SYS.DBMS_STATS", line 10782 ORA-06512: at line 1
• SESSION ID:(246.1241) 2006-06-27 03:40:21.184
• 2006-06-27 03:40:21.184 ORA-12012: error on auto execute of job 201 ORA-12801: error signaled in parallel query server P003 ORA-01555: snapshot too old: rollback segment number 24 with name "_SYSSMU24$" too small ORA-06512: at "SYS.DBMS_STATS", line 10070 ORA-06512: at "SYS.DBMS_STATS", line 10564 ORA-06512: at "SYS.DBMS_STATS", line 10751 ORA-06512: at "SYS.DBMS_STATS", line 10805 ORA-06512: at "SYS.DBMS_STATS", line 10782 ORA-06512: at line 1

Note - The job has completed now! And, I have tried to check the above SQL entry in statspack report / V$sql_Text / V$sql_area, no luck!

Also, UNDO_RBS where _SYSSMU24$ sits has some 27 GB Free space (out of 40 GB total size!)

Any clue on knowing which object had this problem on ORA-1555? Thanks,
Arul.

---

Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jun 27 2006 - 14:36:47 CDT