Ask the Expert

ORA-604 and ORA-4021 errors

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!

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: