Solved

ORA-01000: maximum open cursors exceeded

Posted on 2008-06-13
18
2,573 Views
Last Modified: 2013-12-07
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.
select TFF.FACTFIND_ID

				, SUBMISSIONS.SUBMISSION_NUMBER

			   , parties.cust1

			   , parties.cust2

			   , TFF.AGENT_ID

			   , null

			   , TFF.LEAD_ID

				, TFF.FACTFIND_DATE

			   , TFF.STATUS_DATE

			   , DECODE(SUBMISSIONS.SUBMISSION_NUMBER, 1,'Y','N') rft_flag

			   , 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

			   , TFF.BUILDINGS_RENEWAL_DATE

			   , TFF.BUILDINGS_PROVIDER_TEXT

			   , TFF.CONTENTS_RENEWAL_DATE

			   , TFF.CONTENTS_PROVIDER_TEXT

			   , 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

			   , TFF.STATUS_DATE

			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

					where TNA.NA_NEED_ID = TNND.NA_NEED_ID

					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

			            , MAX(TFEB.DECLINED_FLAG) DECLINED_FLAG

			      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

			            , MAX(TFEB1.DECLINED_FLAG) DECLINED_FLAG

			      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

			            , MAX(TFEB2.DECLINED_FLAG) DECLINED_FLAG

			      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

			            , MAX(TFEB3.DECLINED_FLAG) DECLINED_FLAG

			      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

			      from 

			      (

			              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'

			              and TNIPP.FACTFIND_INDIVIDUAL_ID = tfr1.FACTFIND_INDIVIDUAL_ID  

			              union  

			              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  

			              union  

			              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' 

			              and tnc.FACTFIND_INDIVIDUAL_ID = tfr1.FACTFIND_INDIVIDUAL_ID  

			              union  

			              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  

			              union  

			              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'  

			              and tns.FACTFIND_INDIVIDUAL_ID = tfr1.FACTFIND_INDIVIDUAL_ID  

			              union  

			              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' 

			              and tni.FACTFIND_INDIVIDUAL_ID = tfr1.FACTFIND_INDIVIDUAL_ID  

			      )

			      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

			      from 

			      (

			              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  

			              union  

			              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  

			              union  

			              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  

			              union  

			              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  

			              union  

			              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  

			              union  

			              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

			      where TFR2.FACTFIND_INDIVIDUAL_ID = TNA2.FACTFIND_INDIVIDUAL_ID

			      group by TNA2.FACTFIND_ID

			  	 ) atr

			where TFF.STATUS_CODE_ID = 1008521

			and TFF.FACTFIND_ID = submissions.FACTFIND_ID

			and TFF.FACTFIND_ID = parties.FACTFIND_ID

			and TFF.FACTFIND_ID = expenditure.FACTFIND_ID

			and TFF.FACTFIND_ID = assets.FACTFIND_ID

			and TFF.FACTFIND_ID = liabilities.FACTFIND_ID

			and TFF.FACTFIND_ID = occben.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 = atr.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 = needs.FACTFIND_ID(+)

			and TFF.FACTFIND_ID = sales.FACTFIND_ID(+);

Open in new window

0
Comment
Question by:graham_ball
  • 7
  • 3
  • 3
  • +3
18 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21779828
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.
0
 

Author Comment

by:graham_ball
ID: 21779877
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.
0
 
LVL 16

Expert Comment

by:rolutola
ID: 21779923
What about your OPEN_CURSORS parameter?

R.
0
 

Author Comment

by:graham_ball
ID: 21779948
That's 1024, as I said above.
0
 
LVL 16

Expert Comment

by:rolutola
ID: 21780081
0
 
LVL 34

Expert Comment

by:johnsone
ID: 21780308
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

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21780701
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...
0
 
LVL 47

Expert Comment

by:schwertner
ID: 21784410
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;


Reference
---------

"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
{
 static
 {
     new oracle.jdbc.driver.OracleDriver();
 }

 public static void main(String args[])
 {
     while (true)
     {
         // replace url, uid, and pwd with valid data.
         Connection dbConnection =
              DriverManager.getConnection(
             url, uid, pwd);
         dbConnection.close();
         System.gc();
     }
 }
}


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

Sincerely
Schwertner

0
 

Author Comment

by:graham_ball
ID: 21791501
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.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 47

Expert Comment

by:schwertner
ID: 21791658
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.
0
 

Author Comment

by:graham_ball
ID: 21791693
This is not an application, neither is it expicilty opening cursors.
It is a single sql statement.
0
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 21792171
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.
0
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 21792187
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?
0
 

Author Comment

by:graham_ball
ID: 21792208
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 ?
0
 
LVL 16

Accepted Solution

by:
Milleniumaire earned 500 total points
ID: 21792275
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.
0
 
LVL 47

Expert Comment

by:schwertner
ID: 21792942
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.
0
 

Author Comment

by:graham_ball
ID: 21792955
It is not a procedure.
0
 

Author Closing Comment

by:graham_ball
ID: 31466958
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!
Thanks.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now