Skip to main content
  • EN

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.

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.

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.

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.

 

 

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.

 

 

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


Community Stats

30,108
Posts
114,295
Replies
37,743
Members
Terms & ConditionsCookie 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.