ORA-01037: maximum cursor memory exceeded

From: Tom Parris <tparris_at_fas.harvard.edu>
Date: 1998/01/09
Message-ID: <34B6CCAC.34C9@fas.harvard.edu>#1/1

All,

I hope someone can help me with a problem I have been encountering with Oracle Server 7.3.3 on a Sun UltraSparc I with 128MB of RAM. I realize you all have many other fish to fry and am appreciative in advance of any help you can offer.

The problem is that I have an application that has the potential of generating queries with up to three long IN (...) clauses. When these reach a certain theshold in length, I get the error:

An example of a query that causes the problem is ...

select

    D.Value, D.YearCode, D.PublicationCode, 
    D.IndicatorCode, D.CountryCode, 
    C.COW_CAbbr, C.COW_CName || ' (' || 
    C.PubBegYr || '-' || C.PubEndYr || ')', 
    I.Definition, I.Units

from

keddy.DATA D, keddy.COUNTRIES C, keddy.INDICATORS I where

    D.CountryCode = C.SDI_CCode AND
    D.IndicatorCode = I.Code AND
    D.IndicatorCode IN ('4', '5', '6', '7', '8', '9', 
                        '10', '11', '231') AND
    D.CountryCode IN ('3700', '2111', '800', '4340', 
                      '68', '4500', '7040') AND
    D.YearCode IN ('1970', '1971', '1972', '1973',
'1974',                     '1975', '1976', '1977', '1978', '1979',


                   '1980', '1981', '1982', '1983', '1984', 

                   '1985', '1986', '1987', '1988', '1989',

                   '1990', '1991', '1992', '1993', '1994',

                   '1995')

The code lists are based on user input from checkboxes. Hence, I can't simply replace the "D.YearCode IN" clause with a range. The query works fine if I reduce the number of CountryCodes to 3 or 4. This is by no means the longest query possible.

I have tried to twiddle several init.ora and /etc/system parameters with no change in behaviour. In particular, I have turned on the cursor_space_for_time flag, and upped all of the parameters to correspond to the sample "large" database (e.g., shared_pool_size = 9000000).

Once again. Many many thanks for any help you can provide.

Tom
Tom Parris, Environmental Resources Librarian, Lamont - Level 1, Harvard College Library, Harvard University Cambridge MA 02138 tel. 617-496-6158, fax. 617-496-0440, email. tparris_at_fas.harvard.edu www: http://environment.harvard.edu --
Tom Parris, Environmental Resources Librarian, Lamont - Level 1, Harvard College Library, Harvard University; Cambridge MA 02138 tel. 617-496-6158, fax. 617-496-0440, email. tparris_at_fas.harvard.edu www: http://environment.harvard.edu

Received on Fri Jan 09 1998 - 00:00:00 CST