ORA-4031 and Oracle 7.3

This is just an FYI of something I learned yesterday. (Perhaps the rest of you already knew this or simply don't care, but it was quite an achievement for me!)

We upgraded our data warehouse (about 30 gigs of data & indexes) from 7.2.3 to 7.3.2 over the holidays. Suddenly, some of our users began getting ORA-4031 errors on queries doing large sorts. (The error states: "unable to allocate xxxxx bytes of shared memory...")

I read everything I could find in Oracle's documentation, including their online Support Problem Repository, and the best solution mentioned was to increase Shared_Pool_Size. Well, I'd already figured that, but couldn't understand why I was using up more shared memory going from 7.2 to 7.3. Anyway, I gave the shared_pool_size a good boost (15 to 20 megs), and the users still got the 4031 error.

At this point, I got one of their queries and ran it myself. No problem! Turns out, in the user profiles, I had used the default settings for each of the system resources. (I had also done this with 7.2.) For some reason, however, this was no longer sufficient for 7.3. I changed the PRIVATE SGA from "default" to "unlimited" for the users, and the problem went away.

In a nutshell (I know I ramble--sorry), the 4031 error was apparently being caused by users exceeding their allocated share of the shared memory. Since I have plenty of memory on the machine, granting users unlimited access to it is not a problem.

Hope this was beneficial to someone.

--brad Received on Thu Jan 09 1997 - 00:00:00 CST