ORA-604 and ORA-4021 errors
We are having some problems that lead me to guess that the ORA-604 at SQL level 1 either causes or is caused by the ORA-4031, and then when the query creating the ORA-604 error at SQL level 2 causes the ORA-4021 lockup and that is when I start getting phone calls from people saying that all the applications in the back end have stopped working.
We're having some semi-regular problems in our Oracle database (running on Solaris 9). I'm not sure if this is a DBA problem or a developer/code problem. (I'm not a DBA and I've only ever done front-office development so I'm in somewhat deep water here.)
Here is a snippet of the .trc file where things start to turn sour:
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")
*** 2007-05-03 06:58:20.465
ORA-00604: error occurred at recursive SQL level 2
ORA-04031: unable to allocate 4096 bytes of shared memory
("shared pool","select obj#,type#,ctime,mtim...",
"Typecheck heap","kgghteInit")
*** 2007-05-04 06:20:29.633
ORA-00604: error occurred at recursive SQL level 2
ORA-04021: timeout occurred while waiting to lock object
The pattern that is occurring leads me to guess that the ORA-604 at SQL level 1 either causes or is caused by the ORA-4031, and then when the query creating the ORA-604 error at SQL level 2 causes the ORA-4021 lockup and that is when I start getting phone calls from people saying that all the applications in the back end have stopped working.
Would really appreciate some advice on how to discover what is causing the 604 or 4031!
