ORA-04020 Error
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: ORA-04020 Error

  1. #1
    Join Date
    Feb 2000
    Location
    Alexandria, VA, 22314
    Posts
    41

    Red face

    HI all,

    I was wondering if anyone could help me...i am continually getting then following error:

    ORA-04030: out of process memory when trying to allocate 262144 bytes (cursor
    work he,kllcqas:kllsltba)

    The SQL that is running is very large, and quite complex. When I take the WHERE clause out, it seems to work fine (this is what I would expect)


    My INITODSD.ORA file has the following paramaters:

    open_cursors = 100
    db_block_buffers = 64000
    db_writer_processes = 1
    shared_pool_size = 157286400
    large_pool_size = 31457280


    1) Is there anything that I can change to eliminate this error?
    2) What does this error REALLY mean?

    Thanks in advance,

    Keith

  2. #2
    Join Date
    Jan 2001
    Location
    St. Louis. MO
    Posts
    150
    I know I've experienced this type of error before or something related to this.

    Try increasing the MAX_CURSOR parameter to 500.

    Ed

  3. #3
    Join Date
    Jan 2001
    Location
    St. Louis. MO
    Posts
    150
    I mean the OPEN_CURSOR

    Ed

  4. #4
    Join Date
    Nov 2000
    Posts
    344
    I think it is bombing because your query is so long that Oracle is running out of memory trying to parse it and determine an execution plan.

    Does the where clause contain something like this :

    where some_column in (1,2,3,4,5,6,7....)

    except much, much longer?

    If so, you should do this instead if possible :

    where some_column in (select whatever from wherever)

    where the subquery is a select that will produce the list you need.

    -John

  5. #5
    Join Date
    Jan 2001
    Posts
    2,828
    INCREASE YOUR MAX OPEN CURSORS PARAMETER IN INIT.ORA TO 150 THIS SHOULD SOLVE YOUR PROBLEM

  6. #6
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    have a good high number of open cursors parameter setted up. Then try running the query and see whether it goes successful. If it fails then you might have to increase the shared_pool_size and also make sure that you have a good size of temp table space, if your query were to do some kind of order by clause. Mostly, the OPEN_CURSORS parameter should fix it all. One other thing is that do an explain plan on your query and see how the execution plan goes and you might be able to modify it to use indices and etc

    If you encounter any problems, pl post the problem and the error message...

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  7. #7
    Join Date
    Feb 2000
    Location
    Alexandria, VA, 22314
    Posts
    41

    Still not working

    I increased the OPEN_CURSORS to 500, and I still get the same error message.

    The thing is, the Database was running this query fine on Friday, with not even a hint of a problem. Could it be because there are more instances on the AIX machine swallowing all the memory allocation?

    I reset my INITODSD.ORA file to what is was before (except for OPEN_CURSORS):

    open_cursors = 500
    db_block_buffers = 64000
    db_writer_processes = 1
    dbwr_io_slaves = 2

    large_pool_size = 20971520
    shared_pool_size = 104857600


    Thanks in advance, Keith


  8. #8
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Check your alert log file and let us know of the error that you were getting now after resetting the open_cursors. Also could you let us know of the database version and your db_block_size.

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  9. #9
    Join Date
    Feb 2000
    Location
    Alexandria, VA, 22314
    Posts
    41
    The error i am getting is still

    ORA-04030: out of process memory when trying to allocate 262144 bytes (cursor work he,kllcqas:kllsltba)

    The ALERT LOG is doing fine, there are no errors in there - this error arises from SQL*PLUS.

    The Database Version is ORACLE 8.1.6, and the

    THE DB_BLOCK_SIZE is 2048 - this is merely a development machine.

    db_block_buffers = 64000

    Thanks again, Keith

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width