Skip to content

Loading…

OCIError: ORA-01000: maximum open cursors exceeded #125

Closed
awd-switzerland opened this Issue · 8 comments

7 participants

@awd-switzerland

We execute a lot of similar statements in an ETL process. After a few hundred updates on different entities we get the above error.
It seems the the number of cursors leak - means - cursors are not closed correctly.
Unfortunately I do not have a code snippet I can share to reproduce it.

The problem is as a workaround resolved by calling
ActiveRecord::Base.connection_handler.clear_all_connections!
every one and then (e.g. after processing 100 entities e.g.)

Our Setup:
Oracle 10g
Rails 3.1.1 (same on 3.1.2.rc2)
activerecord-oracle_enhanced-adapter (1.4.0)
ruby-oci8 (2.0.6)

DATABASE Config:
adapter: oracle_enhanced
database:
username:
password:
cursor_sharing: similar
nls_numeric_characters: ".,"

@yahonda
Collaborator

You can find how your application uses cursors.

select user_name,sql_text,count(*) from v$open_cursor
where user_name = 'PUTYOURDATABASEUSERNAME'
group by user_name,sql_text
order by 3 desc;

You can find the current open_cursors initialization parameter value.

select name,value from v$parameter where name = 'open_cursors';
@ebeigarts
Collaborator

The statement pool implementation is not released for oracle enhanced adapter yet, so please try master from github (see #100 for more details). There is also a new param available in database.yml statement_limit (default is 300 cursors).

@cjk

I am seeing this too recently, esp. in conjunction with Delayed_job and after upgrading to oracle-enhanced-adapter v1.4.0

Using Rails 3.1.3

@tamersalama

Seeing a lot of "ORA-01000: maximum open cursors exceeded" here too. Rails 3.1.0, oracle_enhanced 1.4

@ebeigarts
Collaborator

Have you tried oracle_enhanced master with statement_limit?

@cjk

Switching to the master-branch of oracle_enhanced instead of using the gem-version worked for me so far. Perhaps there should be a new release soon?

@cmrichards

I get this too with a oracle_enhanced 1.4. This happened with a recent oracle_enhanced 1.4 that bundler has just downloaded.

@plentz

+1. waiting for a new release :)

@ebeigarts ebeigarts closed this
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.