ORA-01000: maximum open cursors exceeded

The attached code is part of a stored procedure, which is where I'm getting the eeror.
Max cursors is set at 1024 and I just can't see how I'm going anywhere near that.

thanks for your help.
			   , parties.cust1
			   , parties.cust2
			   , TFF.AGENT_ID
			   , null
			   , TFF.LEAD_ID
			   , needs.ip
			   , needs.ci
			   , needs.lcc
			   , needs.lci
			   , needs.savings
			   , needs.investment
			   , needs.pension
			   , needs.annuities
			   , sales.ip
			   , sales.ci
			   , sales.lcc
			   , sales.lci
			   , sales.savings
			   , sales.investment
			   , sales.pension
			   , sales.annuities
			   , EXPENDITURE.DECLINED_FLAG disclosed_expenditure
			   , ASSETS.DECLINED_FLAG disclosed_assets
			   , LIABILITIES.DECLINED_FLAG disclosed_liabilities
			   , OCCBEN.DECLINED_FLAG disclosed_occbens
			   , protection.DECLINED_FLAG disclosed_protection
			   , savings.DECLINED_FLAG disclosed_savings
			   , pensions.DECLINED_FLAG disclosed_pensions
			   , investments.DECLINED_FLAG disclosed_investments
			   , b_and_c_premium.premium
			   , atr.cust1_protection_level
			   , atr.cust1_savings_level
			   , atr.cust1_investment_level
			   , atr.cust1_pension_level
			   , cust_ni.cust1_ip_ni
			   , cust_ni.cust1_ci_ni
			   , cust_ni.cust1_lcc_ni
			   , cust_ni.cust1_lci_ni
			   , cust_ni.cust1_savings_ni
			   , cust_ni.cust1_investments_ni
			   , cust_na.cust1_ip_na
			   , cust_na.cust1_ci_na
			   , cust_na.cust1_lcc_na
			   , cust_na.cust1_lci_na
			   , cust_na.cust1_savings_na
			   , cust_na.cust1_investments_na
			   , atr.cust2_protection_level
			   , atr.cust2_savings_level
			   , atr.cust2_investment_level
			   , atr.cust2_pension_level
			   , cust_ni.cust2_ip_ni
			   , cust_ni.cust2_ci_ni
			   , cust_ni.cust2_lcc_ni
			   , cust_ni.cust2_lci_ni
			   , cust_ni.cust2_savings_ni
			   , cust_ni.cust2_investments_ni
			   , cust_na.cust2_ip_na
			   , cust_na.cust2_ci_na
			   , cust_na.cust2_lcc_na
			   , cust_na.cust2_lci_na
			   , cust_na.cust2_savings_na
			   , cust_na.cust2_investments_na
			from salex.t_ff_factfind@util.world tff
			      select TNLD.FACTFIND_ID
			      , MAX(TNLD.SUBMISSION_NUMBER) submission_number
			      from salex.t_nbmu_log_detail@util.world tnld
			      group by TNLD.FACTFIND_ID
			    ) submissions
			      select TFR.FACTFIND_ID
			         , MAX(DECODE(TFR.ROLE_TYPE_CODE_ID,1008513,TFR.PARTY_ID )) cust1
			         , MAX(DECODE(TFR.ROLE_TYPE_CODE_ID,1008514,TFR.PARTY_ID )) cust2
			      from salex.t_ff_role@util.world tfr
			      group by TFR.FACTFIND_ID
			    )  parties
				   select tna.factfind_id
						, NVL(SUM(DECODE(TNND.NEED_ID,341,1)),0) ip
						, NVL(SUM(DECODE(TNND.NEED_ID,342,1)),0) ci
						, NVL(SUM(DECODE(TNND.NEED_ID,343,1,8,1)),0) lcc
						, NVL(SUM(DECODE(TNND.NEED_ID,340,1)),0) lci
						, NVL(SUM(DECODE(TNND.NEED_ID,346,1)),0) annuities
						, NVL(SUM(DECODE(TNND.NEED_ID,347,1,348,1)),0) savings
						, NVL(SUM(DECODE(TNND.NEED_ID,349,1,350,1)),0) investment
						, NVL(SUM(DECODE(TNND.NEED_ID,344,1,345,1)),0) pension
					from salex.t_na_needs@util.world tna
						, salex.t_na_needs_detail@util.world tnnd
					group by tna.factfind_id
				 ) needs
					select tr.factfind_id
						, NVL(SUM(DECODE(tr.NEED_ID,341,1)),0) ip
						, NVL(SUM(DECODE(tr.NEED_ID,342,1)),0) ci
						, NVL(SUM(DECODE(tr.NEED_ID,343,1,8,1)),0) lcc
						, NVL(SUM(DECODE(tr.NEED_ID,340,1)),0) lci
						, NVL(SUM(DECODE(tr.NEED_ID,346,1)),0) annuities
						, NVL(SUM(DECODE(tr.NEED_ID,347,1,348,1)),0) savings
						, NVL(SUM(DECODE(tr.NEED_ID,349,1,350,1)),0) investment
						, NVL(SUM(DECODE(tr.NEED_ID,344,1,345,1)),0) pension
					from salex.t_recommendations@util.world tr
					where TR.FACTFIND_ID is not null
					and TR.ACCEPTED_CODE_ID = 590
					group by tr.factfind_id
				 ) sales
			      select distinct TFIE.FACTFIND_ID
			            , TFIE.DECLINED_FLAG
			      from salex.t_ff_income_expenditure@util.world tfie
				 ) expenditure
			      select distinct TFA.FACTFIND_ID
			            , TFA.DECLINED_FLAG
			      from salex.t_ff_assets@util.world tfa
				 ) assets
					select distinct TFL.FACTFIND_ID
			            , TFL.DECLINED_FLAG
			      from salex.t_ff_liabilities@util.world tfl
				 ) liabilities
			      select distinct TFOB.FACTFIND_ID
			            , TFOB.DECLINED_FLAG
			      from salex.t_ff_occupational_benefits@util.world tfob
				 ) occben
			      select distinct TFEB.FACTFIND_ID
			      from salex.t_ff_existing_business@util.world tfeb
			      where TFEB.NEED_AREA_CODE_ID = 1008499
			      group by tfeb.factfind_id
				 ) protection
			      select distinct TFEB1.FACTFIND_ID
			      from salex.t_ff_existing_business@util.world tfeb1
			      where TFEB1.NEED_AREA_CODE_ID = 1008500
			      group by tfeb1.factfind_id
				 ) savings
			      select distinct TFEB2.FACTFIND_ID
			      from salex.t_ff_existing_business@util.world tfeb2
			      where TFEB2.NEED_AREA_CODE_ID = 1008501
			      group by tfeb2.factfind_id
				 ) pensions
			      select distinct TFEB3.FACTFIND_ID
			      from salex.t_ff_existing_business@util.world tfeb3
			      where TFEB3.NEED_AREA_CODE_ID = 1008502
			      group by tfeb3.factfind_id
				 ) investments
			      select TFIE1.FACTFIND_ID
			         , SUM(TFIED1.IE_AMT) premium
			      from salex.t_ff_income_expenditure@util.world tfie1
			         , salex.t_ff_income_exp_detail@util.world tfied1
			      where TFIE1.IE_ID = TFIED1.IE_ID
			      and TFIE1.DECLINED_FLAG = 'N'
			      and TFIED1.IE_CODE_ID = 1008556
			      group by TFIE1.FACTFIND_ID
			   ) b_and_c_premium
			      select factfind_id
			            , MAX(DECODE(ROLE_TYPE_CODE_ID||ni_type,'1008513IP','Y')) cust1_ip_ni
			            , MAX(DECODE(ROLE_TYPE_CODE_ID||ni_type,'1008513CI','Y')) cust1_ci_ni
			            , MAX(DECODE(ROLE_TYPE_CODE_ID||ni_type,'1008513LCC','Y')) cust1_lcc_ni
			            , MAX(DECODE(ROLE_TYPE_CODE_ID||ni_type,'1008513LCI','Y')) cust1_lci_ni
			            , MAX(DECODE(ROLE_TYPE_CODE_ID||ni_type,'1008513Savings','Y')) cust1_savings_ni
			            , MAX(DECODE(ROLE_TYPE_CODE_ID||ni_type,'1008513Investments','Y')) cust1_investments_ni
			            , MAX(DECODE(ROLE_TYPE_CODE_ID||ni_type,'1008514IP','Y')) cust2_ip_ni
			            , MAX(DECODE(ROLE_TYPE_CODE_ID||ni_type,'1008514CI','Y')) cust2_ci_ni
			            , MAX(DECODE(ROLE_TYPE_CODE_ID||ni_type,'1008514LCC','Y')) cust2_lcc_ni
			            , MAX(DECODE(ROLE_TYPE_CODE_ID||ni_type,'1008514LCI','Y')) cust2_lci_ni
			            , MAX(DECODE(ROLE_TYPE_CODE_ID||ni_type,'1008514Savings','Y')) cust2_savings_ni
			            , MAX(DECODE(ROLE_TYPE_CODE_ID||ni_type,'1008514Investments','Y')) cust2_investments_ni
			              select 'IP' ni_type  
			                  , tnipp.FACTFIND_ID
			                  , tfr1.ROLE_TYPE_CODE_ID
			              from salex.t_na_ipp@util.world tnipp
			                  , salex.t_ff_role@util.world tfr1  
			              where tnipp.not_important_flag = 'Y'
			              select 'CI'  
			                  , tnci.FACTFIND_ID  
			                  , tfr1.ROLE_TYPE_CODE_ID
			              from salex.t_na_cic@util.world tnci  
			                  , salex.t_ff_role@util.world tfr1  
			              where tnci.not_important_flag = 'Y'  
			              and tnci.FACTFIND_INDIVIDUAL_ID = tfr1.FACTFIND_INDIVIDUAL_ID  
			              select 'LCC'  
			                  , tnc.FACTFIND_ID  
			                  , tfr1.ROLE_TYPE_CODE_ID
			              from salex.t_na_capital@util.world tnc  
			                  , salex.t_ff_role@util.world tfr1  
			              where tnc.not_important_flag = 'Y' 
			              select 'LCI'  
			                  , tnlc.FACTFIND_ID  
			                  , tfr1.ROLE_TYPE_CODE_ID
			              from salex.t_na_life_cover@util.world tnlc  
			                  , salex.t_ff_role@util.world tfr1  
			              where tnlc.not_important_flag = 'Y'  
			              and tnlc.FACTFIND_INDIVIDUAL_ID = tfr1.FACTFIND_INDIVIDUAL_ID  
			              select 'Savings'  
			                  , tns.FACTFIND_ID  
			                  , tfr1.ROLE_TYPE_CODE_ID
			              from salex.t_na_savings@util.world tns  
			                  , salex.t_ff_role@util.world tfr1  
			              where tns.not_important_flag = 'Y'  
			              select 'Investments'  
			                  , tni.FACTFIND_ID  
			                  , tfr1.ROLE_TYPE_CODE_ID
			              from salex.t_na_investments@util.world tni  
			                  , salex.t_ff_role@util.world tfr1  
			              where tni.not_important_flag = 'Y' 
			      group by factfind_id
			  	 ) cust_ni
			      select factfind_id
			            , MAX(DECODE(ROLE_TYPE_CODE_ID||na_type,'1008513IP','Y')) cust1_ip_na
			            , MAX(DECODE(ROLE_TYPE_CODE_ID||na_type,'1008513CI','Y')) cust1_ci_na
			            , MAX(DECODE(ROLE_TYPE_CODE_ID||na_type,'1008513LCC','Y')) cust1_lcc_na
			            , MAX(DECODE(ROLE_TYPE_CODE_ID||na_type,'1008513LCI','Y')) cust1_lci_na
			            , MAX(DECODE(ROLE_TYPE_CODE_ID||na_type,'1008513Savings','Y')) cust1_savings_na
			            , MAX(DECODE(ROLE_TYPE_CODE_ID||na_type,'1008513Investments','Y')) cust1_investments_na
			            , MAX(DECODE(ROLE_TYPE_CODE_ID||na_type,'1008514IP','Y')) cust2_ip_na
			            , MAX(DECODE(ROLE_TYPE_CODE_ID||na_type,'1008514CI','Y')) cust2_ci_na
			            , MAX(DECODE(ROLE_TYPE_CODE_ID||na_type,'1008514LCC','Y')) cust2_lcc_na
			            , MAX(DECODE(ROLE_TYPE_CODE_ID||na_type,'1008514LCI','Y')) cust2_lci_na
			            , MAX(DECODE(ROLE_TYPE_CODE_ID||na_type,'1008514Savings','Y')) cust2_savings_na
			            , MAX(DECODE(ROLE_TYPE_CODE_ID||na_type,'1008514Investments','Y')) cust2_investments_na
			              select 'IP' na_type  
			                  , tnipp1.FACTFIND_ID
			                  , tfr2.ROLE_TYPE_CODE_ID
			              from salex.t_na_ipp@util.world tnipp1
			                  , salex.t_ff_role@util.world tfr2
			              where tnipp1.NOT_APPLICABLE_FLAG = 'Y'
			              and tnipp1.FACTFIND_INDIVIDUAL_ID = tfr2.FACTFIND_INDIVIDUAL_ID  
			              select 'CI'  
			                  , tnci1.FACTFIND_ID  
			                  , tfr2.ROLE_TYPE_CODE_ID
			              from salex.t_na_cic@util.world tnci1 
			                  , salex.t_ff_role@util.world tfr2 
			              where tnci1.NOT_APPLICABLE_FLAG = 'Y'  
			              and tnci1.FACTFIND_INDIVIDUAL_ID = tfr2.FACTFIND_INDIVIDUAL_ID  
			              select 'LCC'  
			                  , tnc1.FACTFIND_ID  
			                  , tfr2.ROLE_TYPE_CODE_ID
			              from salex.t_na_capital@util.world tnc1 
			                  , salex.t_ff_role@util.world tfr2
			              where tnc1.NOT_APPLICABLE_FLAG = 'Y' 
			              and tnc1.FACTFIND_INDIVIDUAL_ID = tfr2.FACTFIND_INDIVIDUAL_ID  
			              select 'LCI'  
			                  , tnlc1.FACTFIND_ID  
			                  , tfr2.ROLE_TYPE_CODE_ID
			              from salex.t_na_life_cover@util.world tnlc1 
			                  , salex.t_ff_role@util.world tfr2
			              where tnlc1.NOT_APPLICABLE_FLAG = 'Y'  
			              and tnlc1.FACTFIND_INDIVIDUAL_ID = tfr2.FACTFIND_INDIVIDUAL_ID  
			              select 'Savings'  
			                  , tns1.FACTFIND_ID  
			                  , tfr2.ROLE_TYPE_CODE_ID
			              from salex.t_na_savings@util.world tns1 
			                  , salex.t_ff_role@util.world tfr2 
			              where tns1.NOT_APPLICABLE_FLAG = 'Y'  
			              and tns1.FACTFIND_INDIVIDUAL_ID = tfr2.FACTFIND_INDIVIDUAL_ID  
			              select 'Investments'  
			                  , tni1.FACTFIND_ID  
			                  , tfr2.ROLE_TYPE_CODE_ID
			              from salex.t_na_investments@util.world tni1 
			                  , salex.t_ff_role@util.world tfr2 
			              where tni1.NOT_APPLICABLE_FLAG = 'Y' 
			              and tni1.FACTFIND_INDIVIDUAL_ID = tfr2.FACTFIND_INDIVIDUAL_ID  
			      group by factfind_id
			  	 ) cust_na
			      select TNA2.FACTFIND_ID
			         , MAX(DECODE(TFR2.ROLE_TYPE_CODE_ID||TNA2.ATR_PRODUCT_TYPE_CODE_ID,10085131008768, TNA2.ATR_LEVEL)) cust1_protection_level
			         , MAX(DECODE(TFR2.ROLE_TYPE_CODE_ID||TNA2.ATR_PRODUCT_TYPE_CODE_ID,10085131008769, TNA2.ATR_LEVEL)) cust1_savings_level
			         , MAX(DECODE(TFR2.ROLE_TYPE_CODE_ID||TNA2.ATR_PRODUCT_TYPE_CODE_ID,10085131008770, TNA2.ATR_LEVEL)) cust1_investment_level
			         , MAX(DECODE(TFR2.ROLE_TYPE_CODE_ID||TNA2.ATR_PRODUCT_TYPE_CODE_ID,10085131008771, TNA2.ATR_LEVEL)) cust1_pension_level
			         , MAX(DECODE(TFR2.ROLE_TYPE_CODE_ID||TNA2.ATR_PRODUCT_TYPE_CODE_ID,10085141008768, TNA2.ATR_LEVEL)) cust2_protection_level
			         , MAX(DECODE(TFR2.ROLE_TYPE_CODE_ID||TNA2.ATR_PRODUCT_TYPE_CODE_ID,10085141008769, TNA2.ATR_LEVEL)) cust2_savings_level
			         , MAX(DECODE(TFR2.ROLE_TYPE_CODE_ID||TNA2.ATR_PRODUCT_TYPE_CODE_ID,10085141008770, TNA2.ATR_LEVEL)) cust2_investment_level
			         , MAX(DECODE(TFR2.ROLE_TYPE_CODE_ID||TNA2.ATR_PRODUCT_TYPE_CODE_ID,10085141008771, TNA2.ATR_LEVEL)) cust2_pension_level
			      from salex.t_na_atr@util.world tna2
			         , salex.t_ff_role@util.world tfr2
			      group by TNA2.FACTFIND_ID
			  	 ) atr
			where TFF.STATUS_CODE_ID = 1008521
			and TFF.FACTFIND_ID = submissions.FACTFIND_ID
			and TFF.FACTFIND_ID = expenditure.FACTFIND_ID
			and TFF.FACTFIND_ID = liabilities.FACTFIND_ID
			and TFF.FACTFIND_ID = protection.FACTFIND_ID(+)
			and TFF.FACTFIND_ID = savings.FACTFIND_ID(+)
			and TFF.FACTFIND_ID = pensions.FACTFIND_ID(+)
			and TFF.FACTFIND_ID = investments.FACTFIND_ID(+)
			and TFF.FACTFIND_ID = cust_ni.FACTFIND_ID(+)
			and TFF.FACTFIND_ID = cust_na.FACTFIND_ID(+)
			and TFF.FACTFIND_ID = b_and_c_premium.FACTFIND_ID(+)
			and TFF.FACTFIND_ID = sales.FACTFIND_ID(+);

Open in new window

Who is Participating?
MilleniumaireConnect With a Mentor Commented:
I read somewhere that cursors are allocated 64 at a time up to OPEN_CURSORS, so I would set it to at least 64.  There is no harm in setting a high value other than a little more memory being used for each session.

I confess I'm not exactly sure how cursors are allocated for remote connections, but based on your problem it obviously isn't done very effeciently!!!  If it isn't too much trouble then you should increase the open_cursors init.ora parameter on the remote database as I don't think you'll be able to change this value at the session level as you don't have control over the remote session setting.  Can't remember if this is a dynamic setting, so you may have to bounce the database for it to take effect.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the problem is eventually not your code (alone), but all the users/applications using (ie opening) cursors with not closing them...
this is one of the "problematic" situations where your code might not be the culprit, but for example some legacy application.

you might try to increase the setting to allow more open cursors, but if there is a leak in some legacy application, only correcting the actual source of the problem will help ultimately.
graham_ballAuthor Commented:
I was going to increase the max open cursors as a last resort, but I wanted to eliminate any other cause first.
I'm running this on a test database, where I am the only user.
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Richard OlutolaConsultantCommented:
What about your OPEN_CURSORS parameter?

graham_ballAuthor Commented:
That's 1024, as I said above.
Richard OlutolaConsultantCommented:
johnsoneSenior Oracle DBACommented:
Below is the description of the OPEN_CURSORS parameter from the Oracle documentation.  It is a session level parameter, not a database level parameter.  You must be exceeding 1024 cursors in your session, not database wide.

Also, it may not be just one query that is causing the problem.  That is where the problem manifests itself.  Quite commonly I have seen processes explicitly open cursors and not close them assuming the database will do it when the process diconnects.  A bad practice, but some people do it.
OPEN_CURSORS specifies the maximum number of open cursors (handles to private SQL areas) a session can have at once. You can use this parameter to prevent a session from opening an excessive number of cursors. This parameter also constrains the size of the PL/SQL cursor cache which PL/SQL uses to avoid having to reparse as statements are reexecuted by a user.
It is important to set the value of OPEN_CURSORS high enough to prevent your application from running out of open cursors. The number will vary from one application to another. Assuming that a session does not open the number of cursors specified by OPEN_CURSORS, there is no added overhead to setting this value higher than actually needed.

Open in new window

Guy Hengel [angelIII / a3]Billing EngineerCommented:
side note:
* replace UNION by UNION ALL whenever possible (UNION performs a implicit DISTINCT ...)
* use JOIN  / LEFT JOIN syntax instead of the "old" (+) method
* try to create a (materialized) view on the (remote) server, running/returning some data pre-worked, to reduce the network traffic between the 2

I did not see any function call in the sql, which might open a hidden cursor...
Your code also can do this.
Your program has to have EXCEPTION section and by error to close the opened cursors and release all resources. Possibly not onlythe code  you paste, but also others units running under the same account.

To investigate

set trimspool on
set linesize 1000
break on report
comp sum of curs on report
select SUBSTR(User_Name,1,20) User_Name, SID, count(*) Curs from v$open_cursor group by User_Name,
SID order by User_Name, SID;

I have answered such question and this is the summary of my answers:

You have a JDBC application which runs for several hours.  It then returns the following error:
 ORA-1000 Maximum open cursors exceeded

The open_cursors parameter in init.ora is currently set to a number in excess of the maximum number of cursors you expect your application to have open at any one time.  Increasing open_cursors extends the period of time before failure, but does not resolve the issue.
Solution Description
Typically, in Java, when an object goes out of scope, it is automatically garbage collected, but the Java specification does not place any specific requirements on the JVM regarding when (or even if) this will occur for any
particular object.  Therefore, do not rely on finalizers to close your cursors.

Explicity close all cursors ResultSet.close() and Statement.close() when you no longer need the cursor.  This ensures that the corresponding server-side cursors are closed, preventing the ORA-1000 error.

You can query the SQL_TEXT column in V$OPEN_CURSOR view from the schema to determine which cursors are not being closed.  For example:

  select sql_text from v$open_cursor;


"Oracle8i JDBC Developer's Guide and Reference", Chapter 3 'Basic Features',
Section 'First Steps in JDBC'  

1. You should not use the finally block to reliably close cursors.
2. You should design your code to ensure that the resultSet/cursor and staements go out of scope after the close() method is invoked. This is necessary in order to execute garbage collection and reclaim the memory allocated for cursors

The problem arises often and appear to only have open cursors when using stored procedures and functions.
Try two stragies to resolve the problem:

1) Increase the value for open_cursors in our init.ora file from the default of 50 to 200

2) Have a thread running in our connectionpool class which looks for idle connections older than XX minutes, and then closes them and replaces them by new connections.

The effect of 2) should be to reduce the number of cursors open concurrently. By staggering the open and close we shouldn't put a significant overhead on Oracle or our web splication.
I think Connection.close() will close the resultset of the connection and will help to clear the memory as in the example below

public class JDBCLeak
     new oracle.jdbc.driver.OracleDriver();

 public static void main(String args[])
     while (true)
         // replace url, uid, and pwd with valid data.
         Connection dbConnection =
             url, uid, pwd);

The following code snippet is always a good coding pattern to follow when using Statement, PreparedStatement and ResultSet.

Connection conn = getConnection();
PreparedStatement ps = null;
ResultSet rs = null;

try {
 ps = conn.prepareStatement(...);
 rs = ps.execute...

catch (SQLException sqe) {
 /* should I handle it? */
finally {
 if (rs != null) try { rs.close(); } catch (SQLException) { /* should I handle it? */ }
 if (ps != null) try { ps.close(); } catch (SQLException) { /* should I handle it? */ }

Thus, regardless of what happens, you will always correctly close the ResultSet and PreparedStatement. This of course only works if you don't need to pass the ResultSet back and can do all your work within the try {} block.

If you use Oracle 8.1.7:
Some colleagues solve this problem in 8.1.7 setting the init<sid>.ora parameter TIMED_STATICS = TRUE. Also try to use the latest version of 8.1.7 and patchset


graham_ballAuthor Commented:
What governs the number of cursors that get opened?
I have only got 29 select statements in that piece of code and can't see how it can get anywhere near 100 let alone 1024.
Interestingly, I only have to remove one cursor (any one) from the statement to get it to work.
The application and the procedures.
In many cases the WEB applications open a bunch of sessions with cursors
but do not close them if they are unused.
Also PL/SQL procedures without EXCEPTION handlers leave open cursors after failing.
graham_ballAuthor Commented:
This is not an application, neither is it expicilty opening cursors.
It is a single sql statement.
I notice you are using db links to access all the tables.  Do you get the same error if you connect directly to the database referenced by the db links and remove the db links from the sql?

I'm wondering if this is an issue relating to remote database access with such a large, complicated statement.
Following on from my previous post...

Although your maximum cursors is set high for your current local session, is this session value also applied to the remote session that will be created to satisfy the db links used by your query?
graham_ballAuthor Commented:
It runs fine on the 'linked' database even with an open cursors setting of 50.
So it looks as if it's the link that's the problem.

Is upping the limit on the linked database tthe answer then ?
seems your procedure has not Exception section.
You run and run and run the procedure and
it fails, fails , fails ...

As Milleniumaire mentioned there are cursors to remote DB, to local DB
and possibly the stay opened.

If you catch the error and gracefully close the procedure I hope
the cursors will be closed.
graham_ballAuthor Commented:
It is not a procedure.
graham_ballAuthor Commented:
It would certainly appear that the dblink uses up cursors. I changed the sql to point to our test database, which we had changed from 50 to 512 open cursors and it worked fine; somewhat faster too!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.