Re: can u help me with this ?

In article <388756C0.AE6FAF66_at_questone.com>,   parora_at_questone.com wrote:
> Hi all
>
> I got the following error on execution of a pretty long query
>
> from ms1_tab
> *
> ERROR at line 2:
> ORA-01037: maximum cursor memory exceeded
>
> Infact the query size was larger than this ...when we executed a
> larger query than this (around 9000 bytes) it gave me this error
>
> ORA-04031: unable to allocate 24 bytes of shared memory ("shared
> pool","select pd_id,mkt_id from ms1...","sql area","logdef : apaclg")
>
> I also tried increasing the shared pool area by setting the
> SHARED_POOL_SIZE to 200mb
> but still i am getting the same error
>
> do i need to look at some other parameters too ??plz help
>
> Thanks & Regards
> ~Parvinder
>
>

I have never seen this so I looked it up: 01037, 00000, "maximum cursor memory exceeded" // *Cause: Attempting to process a complex sql statement which consumed all
// available memory of the cursor. // *Action: Simplify the complex sql statement.

The 04031 is a memory error in the shared pool. Both these problems could relate to the fact you need a bigger shared pool setting in your init.ora file.

If your database instance has been running a long time flushing the pool might work: alter system flush shared_pool;

If one or both of the statements work right after flushing the pool but break again in a short time period I would say increasing the pool size would be the fix. There is also a couple of init.ora parameters related to cursors you may want to look into.

Also I believe that an SQL statement is limited to 64K; if you are exceeding this you probably need to use a program or pl/sql.

--
Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Jan 20 2000 - 15:13:39 CST