Skip to main content
Question

Cursor error when mass reading from an Oracle database

M

Hi all, fairly new to FME and can't find much about this topic. I am using an FME workbench to move over a lot of data from an oracle database to a Microsoft SQL server. This is done by using a lot of SQL creators. It has been working fine but I think I have reached the limit of the Oracle I am using. My error is "did not succeed; error was `ORA-01000: maximum open cursors exceeded". I was trying to keep this all in one workbook - but I am not sure how with this error? I tried creating a separate database instance but that didn't work.

5 replies

erik_jan
Contributor
Forum|alt.badge.img+17
  • erik_janContributor
  • Contributor
  • August 20, 2018

In the settings for the Oracle database a setting exists to define the number of open cursors (queries that the database can perform at a certain time).

This is by default 150.

The solution is to set this setting to a higher number (e.g. 250) and restart the database.

This is usually a task for a DBA.

M
Forum|alt.badge.img

Hi,

 

maybe you should try to reduce the SQL creators in some way, maybe you can perform a query that is more general an do filtering with FME transformers.

Or you can toggle off the option for permanent Connection. That can be found in the parameters of the Oracle Connection and should close the connection when querying finished.

M
  • martyn27
  • Author
  • August 21, 2018
erik_jan wrote:

In the settings for the Oracle database a setting exists to define the number of open cursors (queries that the database can perform at a certain time).

This is by default 150.

The solution is to set this setting to a higher number (e.g. 250) and restart the database.

This is usually a task for a DBA.

Thanks, I figured that was the case. Was hoping to find a way to somehow close these when done or wait until they are available. Working on a second workbench has fixed this for now but not my ideal soloution. Thanks for the reply.

 

 

M
  • martyn27
  • Author
  • August 21, 2018
maikhertel wrote:

Hi,

 

maybe you should try to reduce the SQL creators in some way, maybe you can perform a query that is more general an do filtering with FME transformers.

Or you can toggle off the option for permanent Connection. That can be found in the parameters of the Oracle Connection and should close the connection when querying finished.

I understand that this is probably a very inefficient way of working. This is my first major FME project and was just keeping it simple for now. The toggle option for permanent connection I can only see on my readers - not on the actual database connection. I stopped using readers and started using SQL creators.

 

 

erik_jan
Contributor
Forum|alt.badge.img+17
  • erik_janContributor
  • Contributor
  • August 21, 2018
martyn27 wrote:
Thanks, I figured that was the case. Was hoping to find a way to somehow close these when done or wait until they are available. Working on a second workbench has fixed this for now but not my ideal soloution. Thanks for the reply.

 

 

The issue is: it is not just the queries that you post to the database. Behind the scene Oracle performs a lot of other queries (Spatial and non-Spatial index tables, privileges and other data dictionary queries) to execute your query. And that can add up to the larger number that surpasses the open_cursors limit.

 

 

Reply


Helpful Members This Week

Community Stats

30,958
Posts
117,333
Replies
38,746
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.