Re: Max Open Cursors

"George Fields" <WalterFields_at_jhuapl.edu> wrote in message news:3E8F1386.1F0EFF89_at_jhuapl.edu...
> Hi,
>
> I'm using implicit cursors and have gotten an ORA-01000: maximum open
> cursors exceeded.
>
> We are running Oracle 8.0.5
>
> 1. This occurs with a maximum cursors set to 900 and I'd rather not go
> much higher.
> 2. I know that Oracle does not close most cursors until the application
> is exited, so the cursors can be used over again if a process is run
> more often.
>
> How can I force Oracle to close the open cursors?
>
> What are the trade-offs if I increase the maximum cursors to a larger
> number (like 2000)?
>
> Thanks,
> George Fields
>
>
>

George,
If you are using PL/SQL then fine with implicit cursors. They will get closed upon exit of the procedure or function. If you are using an api (eg oci, odbc, ado, jdbc, etc.) then you need to either: 1. Open a cursor once and reuse it for the life of an application. (using bind variables this is a very efficient method; you rebind and reexecute when your bind variable values change)
or
2. Close the cursor when you are done.

You probably have a leak in your application - opening a cursor again and again, whereas if you opened it once and rebound the bind variables you would be all set. If you aren't using bind varibles then you are signifigantly hurting your performance and scalability.(and need to close the cursor every time)

I would get off 8.05 asap and go to at least 8.1.7.4 or later.

Jim Received on Sat Apr 05 2003 - 17:40:37 CST