Links
• Home
• Oracle DBA Forum
Frequent Oracle Errors
•TNS:could not resolve the connect identifier specified
•Backtrace message unwound by exceptions
•invalid identifier
•PL/SQL compilation error
•internal error
•missing expression
•table or view does not exist
•end-of-file on communication channel
•TNS:listener unknown in connect descriptor
•insufficient privileges
•PL/SQL: numeric or value error string
•TNS:protocol adapter error
•ORACLE not available
•target host or object does not exist
•invalid number
•unable to allocate string bytes of shared memory
•resource busy and acquire with NOWAIT specified
•error occurred at recursive SQL level string
•ORACLE initialization or shutdown in progress
•archiver error. Connect internal only, until freed
•snapshot too old
•unable to extend temp segment by string in tablespace
•Credential retrieval failed
•missing or invalid option
•invalid username/password; logon denied
•unable to create INITIAL extent for segment
•out of process memory when trying to allocate string bytes
•shared memory realm does not exist
•cannot insert NULL
•TNS:unable to connect to destination
•remote database not found'>ora-02019
•exception encountered: core dump
•inconsistent datatypes
•no data found
•TNS:operation timed out
•PL/SQL: could not find program
•existing state of packages has been discarded
•maximum number of processes exceeded
•error signaled in parallel query server
•ORACLE instance terminated. Disconnection forced
•TNS:packet writer failure
•see ORA-12699
•missing right parenthesis
•name is already used by an existing object
•cannot identify/lock data file
•invalid file operation
•quoted string not properly terminated
ORA-01555: snapshot too old: rollback segment number 24 with name "_SYSSMU24$ " t

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

2006-06-27       - By arul kumar


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 (See ORA-01555.ora-code.com) 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 (See ORA-12012.ora-code.com): error on auto execute of job 201
ORA-12801 (See ORA-12801.ora-code.com): error signaled in parallel query server
P003
ORA-01555 (See ORA-01555.ora-code.com): snapshot too old: rollback segment number
24 with name "_SYSSMU24$" too small
ORA-06512 (See ORA-06512.ora-code.com): at "SYS.DBMS_STATS", line 10070
ORA-06512 (See ORA-06512.ora-code.com): at "SYS.DBMS_STATS", line 10564
ORA-06512 (See ORA-06512.ora-code.com): at "SYS.DBMS_STATS", line 10751
ORA-06512 (See ORA-06512.ora-code.com): at "SYS.DBMS_STATS", line 10805
ORA-06512 (See ORA-06512.ora-code.com): at "SYS.DBMS_STATS", line 10782
ORA-06512 (See ORA-06512.ora-code.com): 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@(protected)
(J000)

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


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