ORA-01555: snapshot too old: rollback segment number 24 with name "_SYSSMU24$" too small
- From: arul kumar <arul76_2000@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 27 Jun 2006 12:36:47 -0700 (PDT)
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@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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
