ORA-01000: maximum open cursors exceeded

Posted on 2008-06-13
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.
			   , parties.cust1
			   , parties.cust2
			   , TFF.AGENT_ID
			   , null
			   , TFF.LEAD_ID
			   , needs.ip
			   , needs.lcc
			   , needs.lci
			   , needs.savings
			   , needs.investment
			   , needs.pension
			   , needs.annuities
			   , sales.ip
			   , 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 tff
			      select TNLD.FACTFIND_ID
			      , MAX(TNLD.SUBMISSION_NUMBER) submission_number
			      from 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 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 tna
						, 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 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 tfie
				 ) expenditure
			      select distinct TFA.FACTFIND_ID
			            , TFA.DECLINED_FLAG
			      from tfa
				 ) assets
					select distinct TFL.FACTFIND_ID
			            , TFL.DECLINED_FLAG
			      from tfl
				 ) liabilities
			      select distinct TFOB.FACTFIND_ID
			            , TFOB.DECLINED_FLAG
			      from tfob
				 ) occben
			      select distinct TFEB.FACTFIND_ID
			      from tfeb
			      where TFEB.NEED_AREA_CODE_ID = 1008499
			      group by tfeb.factfind_id
				 ) protection
			      select distinct TFEB1.FACTFIND_ID
			      from tfeb1
			      where TFEB1.NEED_AREA_CODE_ID = 1008500
			      group by tfeb1.factfind_id
				 ) savings
			      select distinct TFEB2.FACTFIND_ID
			      from tfeb2
			      where TFEB2.NEED_AREA_CODE_ID = 1008501
			      group by tfeb2.factfind_id
				 ) pensions
			      select distinct TFEB3.FACTFIND_ID
			      from tfeb3
			      where TFEB3.NEED_AREA_CODE_ID = 1008502
			      group by tfeb3.factfind_id
				 ) investments
			      select TFIE1.FACTFIND_ID
			         , SUM(TFIED1.IE_AMT) premium
			      from tfie1
			         , 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 tnipp
			                  , tfr1  
			              where tnipp.not_important_flag = 'Y'
			              select 'CI'  
			                  , tnci.FACTFIND_ID  
			                  , tfr1.ROLE_TYPE_CODE_ID
			              from tnci  
			                  , 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 tnc  
			                  , tfr1  
			              where tnc.not_important_flag = 'Y' 
			              select 'LCI'  
			                  , tnlc.FACTFIND_ID  
			                  , tfr1.ROLE_TYPE_CODE_ID
			              from tnlc  
			                  , 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 tns  
			                  , tfr1  
			              where tns.not_important_flag = 'Y'  
			              select 'Investments'  
			                  , tni.FACTFIND_ID  
			                  , tfr1.ROLE_TYPE_CODE_ID
			              from tni  
			                  , 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 tnipp1
			                  , 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 tnci1 
			                  , 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 tnc1 
			                  , 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 tnlc1 
			                  , 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 tns1 
			                  , 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 tni1 
			                  , 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 tna2
			         , 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

Question by:graham_ball
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
  • 3
  • +3
LVL 143

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.

Author Comment

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.
LVL 16

Expert Comment

by:Richard Olutola
ID: 21779923
What about your OPEN_CURSORS parameter?

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.


Author Comment

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

Expert Comment

by:Richard Olutola
ID: 21780081
LVL 35

Expert Comment

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

LVL 143

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...
LVL 48

Expert Comment

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;


"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



Author Comment

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.
LVL 48

Expert Comment

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.

Author Comment

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

Expert Comment

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.
LVL 16

Expert Comment

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?

Author Comment

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 ?
LVL 16

Accepted Solution

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.
LVL 48

Expert Comment

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.

Author Comment

ID: 21792955
It is not a procedure.

Author Closing Comment

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!

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

622 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