Home » SQL & PL/SQL » SQL & PL/SQL » Maximum Open Cursors Exceeded
Maximum Open Cursors Exceeded [message #35652] Thu, 04 October 2001 04:46 Go to next message
Vijay J
Messages: 1
Registered: October 2001
Junior Member
i am getting this error through my stored procedure,
i want to know where to increase the maximum no of cursors.
1> is it in file.
2> is it a user parameter.
what is it ....
Plz help me
i am desparate

----------------------------------------------------------------------
Re: Maximum Open Cursors Exceeded [message #35654 is a reply to message #35652] Thu, 04 October 2001 05:12 Go to previous messageGo to next message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
maximum no of cursors should be set in intialization file of DB. (e.b init.ora).

get current setting using following query:
select value,name from v$parameter where name like 'open_cursors';

SURESH

----------------------------------------------------------------------
Re: Maximum Open Cursors Exceeded [message #35661 is a reply to message #35652] Thu, 04 October 2001 07:13 Go to previous messageGo to next message
sriram
Messages: 58
Registered: September 2000
Member
Hi Vijay!

I have some answer for your query. Here it goes:
When ever a sql/PLSQL is issued...Oracle creates ...a) Shared SQL Area b) Private SQL Area for execution. Shared Area contains all the general anc common info about the query. While Private Area contains most specific data like parameters etc. Now in a Private SQL area, there are a) Persistent Memory Area and b) Runtime Area.
This Runtime Area gets deallocated automatically when your SQL / PLSQL scop is over. Persistent areas have to be deallocated manully by using Close cursor if u are using one.
If a long running process is going on....there may be many cursors opened (as implicit cursors are used for fetching data by ORACLE)
But The initSID.ora file specifies a parameter OPEN_CURSORS which is set to 50 by default.

Now, down the database instance and then change in the initSID.ora file, increase 50 to 350 say.
Now start up the Instance and there would be no problems...
get back to me if any problems...

have a great day!
Sriram

----------------------------------------------------------------------
Re: Maximum Open Cursors Exceeded [message #35683 is a reply to message #35661] Fri, 05 October 2001 06:32 Go to previous message
sriram
Messages: 58
Registered: September 2000
Member
Hi Vijay,

You are going in the right direction.
If ORCL is your SID , then initORCL.ora would certainly be your initialization file.
If OPEN_CURSORS is not there in your initORCL.ora file , then follow the following steps:

a) Down the database instance.
b) Take a backup of initORCL.ora file.
(Just in case some thing happened/some parameter got deleted , then we could use this copy.)
c) Open the file initORCL.ora.
d) After the last entry , add a line
OPEN_CURSORS = 350.
e) Save the file and close.
f) Start up the database.

Yup, your done.

Please get back if any problems.

Regards
Sriram

----------------------------------------------------------------------
Previous Topic: Re: Pass Table Name as a Variable
Next Topic: Transposing a n/n table
Goto Forum:
  


Current Time: Fri Mar 29 10:17:06 CDT 2024