In this Document
Symptoms
Cause
Solution
References
Applies to:
Oracle Server - Enterprise Edition - Version: 10.1.0.5 and later [Release: 10.1 and later ]Information in this document applies to any platform.
Symptoms
If we see the errors below in alert.log:
Sat Sep 18 03:02:44 2010
Errors in file /oracle/product/ora_5457.trc:
ORA-00600: internal error code, arguments: [kgmgchd1], [], [], [], [], [], [], []
Sat Sep 18 03:03:10 2010
Errors in file /oracle/product/admin/cjq0_1909.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 4096 bytes of shared memory ("shared pool","select job, nvl2(last_date, ...","Typecheck heap","kgghteInit")
Errors in file /oracle/product/ora_5457.trc:
ORA-00600: internal error code, arguments: [kgmgchd1], [], [], [], [], [], [], []
Sat Sep 18 03:03:10 2010
Errors in file /oracle/product/admin/cjq0_1909.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 4096 bytes of shared memory ("shared pool","select job, nvl2(last_date, ...","Typecheck heap","kgghteInit")
In the trace file generated, in Call Stack Trace section, we can see one or more of the following functions:
kgmgchd <- xtypls <- qctopls <- qctcopn <- qctcpqb <- qctcpqbl
The call stack can vary depending on the affected object. In this specific case, it was demonstrated that ORA-4031 were a side effect of ORA-600 and implicit what caused ORA-600.
Cause
This issue is caused by a mismatch in the timestamp information with the dependent objects and invalid objects. It may not be a dictionary corruption problem.
This can also cause problems during upgrades and PLSQL validations.
1. Timestamp mismatch between dependent objects can be checked using the following script.:
select do.obj# d_obj,do.name d_name, do.type# d_type,
po.obj# p_obj,po.name p_name,
to_char(p_timestamp,'DD-MON-YYYY HH24:MI:SS') "P_Timestamp",
to_char(po.stime ,'DD-MON-YYYY HH24:MI:SS') "STIME",
decode(sign(po.stime-p_timestamp),0,'SAME','*DIFFER*') X
from sys.obj$ do, sys.dependency$ d, sys.obj$ po
where P_OBJ#=po.obj#(+)
and D_OBJ#=do.obj#
and do.status=1 /*dependent is valid*/
and po.status=1 /*parent is valid*/
and po.stime!=p_timestamp /*parent timestamp not match*/
order by 2,1;
po.obj# p_obj,po.name p_name,
to_char(p_timestamp,'DD-MON-YYYY HH24:MI:SS') "P_Timestamp",
to_char(po.stime ,'DD-MON-YYYY HH24:MI:SS') "STIME",
decode(sign(po.stime-p_timestamp),0,'SAME','*DIFFER*') X
from sys.obj$ do, sys.dependency$ d, sys.obj$ po
where P_OBJ#=po.obj#(+)
and D_OBJ#=do.obj#
and do.status=1 /*dependent is valid*/
and po.status=1 /*parent is valid*/
and po.stime!=p_timestamp /*parent timestamp not match*/
order by 2,1;
2. Invalid objects can be checked using the following script.:
SELECT status, object_id, object_type, owner||'.'||object_name "OWNER.OBJECT"
FROM dba_objects
WHERE status != 'VALID'
ORDER BY 4,2;
FROM dba_objects
WHERE status != 'VALID'
ORDER BY 4,2;
Solution
1. If the query for timestamp mismatch reports any objects, then you can recompile or recreate these objects.
2. If the query for invalid objects reports any rows, validate all invalid objects using:
Note 300056.1 Debug and Validate Invalid Objects
References
NOTE:300056.1 - Debug and Validate Invalid Objects
