Q

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!

The ORA-4031 error is the root cause of your problem. This error is indicating that not enough memory has been allocated for the Oracle instance. The component that is lacking the memory is the Shared Pool. You'll have to increase the SHARED_POOL_SIZE initialization parameter and then bounce the instance. You can find the current setting of this parameter by querying V$PARAMETER.
This was first published in May 2007

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close