Open Cursor error
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Open Cursor error

Hybrid View

  1. #1
    Join Date
    Nov 2000
    Posts
    62
    Java developer said that when he run sql statement embeded with Java , he got the following error messages.
    The sql statement is the very simple insert and update statement.

    Ora - 01000
    01000, 00000, "maximum open cursors exceeded"
    // *Cause:
    // *Action:
    and
    Ora- 00604
    "error occurred at recursive SQL level %s"
    // *Cause: An error occurred while processing a recursive SQL statement
    // (a statement applying to internal dictionary tables).
    // *Action: If the situation described in the next error on the stack
    // can be corrected, do so; otherwise contact Oracle Support.

    I checked open_cursor parameter in init.ora file.
    It wat set to 200. I think that It seems OK.

    I issued the following query
    select * from v$open_query and only returns 14 rows.

    I don't know what to do except increase the open_cursor parameter. This will be the last choice since the production database(24*7).
    What do you think about this?


  2. #2
    Join Date
    Nov 2000
    Posts
    344
    Are you talking about v$Open_cursor? I don't have a v$Open_query in my 8.1.6 database.

    If so, v$open_cursor does not show recursive cursors that are open. These are internal queries that Oracle has to run to complete your queries. There may be enough internal queries that are using up your limit on open cursors.

    I don't know of a way around this other than to change the init.ora parameter, but there may be a way that I am unaware of. ( I do know that you can't change this parameter on the fly with an 'alter system' command)

    -John

  3. #3
    Join Date
    Nov 2000
    Posts
    62

    V$OPEN_CURSOR

    Sorry I mean v$open_cursor;

    Are you sure about open_cursor parameter can be changed dynamically?

    If it is possible, It would be great.

    Thank you for the help.


  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    he means you cant, only way is to bounce the database

  5. #5
    Join Date
    Nov 2000
    Posts
    62

    How to close cursor

    Before increase the Open_cursor parameter,
    How can I close open cursors?

    I know there is command to close cached open cursors.
    I tried to use the following command, but didn't work.

    DECLARE
    close_cursor Boolean;
    BEGIN
    DBMS_SESSION.set_close_cached_open_cursors (close_cursor , True);
    end;
    /
    Any suggestion?



  6. #6
    Join Date
    Nov 2000
    Posts
    62
    Sorry I put the wrong argument.
    the following was worked.

    DECLARE
    close_cursor Boolean:=True;
    BEGIN
    DBMS_SESSION.set_close_cached_open_cursors (close_cursor);
    end;



  7. #7
    Join Date
    Jan 2001
    Posts
    216
    If we close all the cursors that are opened in our stored procedures will that not work ? The reason is, when we run traffic on our database trying to see how it withstands heavy load, it often gives this max cursors exceeded error. Increasign the init.ora only postpones the error. And the solution described above would require user intervention. Isnt there something automatic ?

    Appreciate your help

  8. #8
    Join Date
    Oct 2000
    Posts
    123
    I have experienced this before, believe me there must be something wrong within your code that the opening cursor can not be "closed" for some reason. Always check your code or logic before increasing or closing the cursor manually. The problem can not be solved just by increasing the max cursors sometimes, and this increased "big max cursor value" probably exceeded without carefully coded.

    Take care.

  9. #9
    Join Date
    Nov 2000
    Posts
    62
    Thank you for the help.

    I agree with you.
    All oracle documentation and my research tells me that Java developer didn't close cursor appropriately.
    I told the developer the importance of the closing cursor, but he said that he closed all the cursor explicitly.
    Since I don't know the Java programming at all, I can not say anything more. what can i say?
    Next time, when he runs the same query again, I want to issue select * from v$open_cursor to see the how many curosor and which statement opens the cursor without closing it.






  10. #10
    Join Date
    Feb 2001
    Posts
    8
    We have faced the same problem here in our company. The problem however turned out to be that in the java code, the code always had stmt.close() missing form a lot of cases where they had executed a query. When this was added, the problem went away.

    Regards



    Puneet Sachdev

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