Too many open cursors ORA-01000: maximum open cursors exceeded

[lydianblues]

With oci8 2.0.4 and oracle-enhanced 1.3.0 on Rails3 Beta4:

In loading a test database, I have a simple loop that does this:

1.upto(200) do |i|
prod = Towel.create(:name => "Item#{i}")
end

"Towel" is just some active record model class.

It worked fine on Rails 2.3.8, and i am not doing any cursor
management myself.

It looks like somewhere in the layers under my app, something is
creating cursors
but not closing them...

Any ideas on how to debug this?

/opt/rvm/gems/ruby-1.9.2-head@rails3/gems/activerecord-3.0.0.beta4/lib/
active_record/connection_adapters/abstract_adapter.rb:210:in `rescue
in log': OCIError: ORA-00604: error occurred at recursive SQL level 1
(ActiveRecord::StatementInvalid)
ORA-01000: maximum open cursors exceeded: SELECT
"PRODUCTS_SEQ".NEXTVAL id FROM dual
from /opt/rvm/gems/ruby-1.9.2-head@rails3/gems/
activerecord-3.0.0.beta4/lib/active_record/connection_adapters/
abstract_adapter.rb:200:in `log'
from /opt/rvm/gems/ruby-1.9.2-head@rails3/gems/activerecord-
oracle_enhanced-adapter-1.3.0/lib/active_record/connection_adapters/
oracle_enhanced_adapter.rb:1726:in `log'
from /opt/rvm/gems/ruby-1.9.2-head@rails3/gems/activerecord-
oracle_enhanced-adapter-1.3.0/lib/active_record/connection_adapters/
oracle_enhanced_adapter.rb:1609:in `select'
from /opt/rvm/gems/ruby-1.9.2-head@rails3/gems/
activerecord-3.0.0.beta4/lib/active_record/connection_adapters/
abstract/database_statements.rb:7:in `select_all'
from /opt/rvm/gems/ruby-1.9.2-head@rails3/gems/
activerecord-3.0.0.beta4/lib/active_record/connection_adapters/
abstract/query_cache.rb:56:in `select_all'
from /opt/rvm/gems/ruby-1.9.2-head@rails3/gems/
activerecord-3.0.0.beta4/lib/active_record/connection_adapters/
abstract/database_statements.rb:13:in `select_one'
from /opt/rvm/gems/ruby-1.9.2-head@rails3/gems/activerecord-
oracle_enhanced-adapter-1.3.0/lib/active_record/connection_adapters/
oracle_enhanced_adapter.rb:642:in `next_sequence_value'
from /opt/rvm/gems/ruby-1.9.2-head@rails3/gems/
activerecord-3.0.0.beta4/lib/active_record/persistence.rb:202:in
`create'
from /opt/rvm/gems/ruby-1.9.2-head@rails3/gems/
activerecord-3.0.0.beta4/lib/active_record/timestamp.rb:51:in `create'
from /opt/rvm/gems/ruby-1.9.2-head@rails3/gems/
activerecord-3.0.0.beta4/lib/active_record/callbacks.rb:294:in `block
in create'
from /opt/rvm/gems/ruby-1.9.2-head@rails3/gems/
activesupport-3.0.0.beta4/lib/active_support/callbacks.rb:412:in
`_run_create_callbacks'
from /opt/rvm/gems/ruby-1.9.2-head@rails3/gems/
activerecord-3.0.0.beta4/lib/active_record/callbacks.rb:294:in
`create'
from /opt/rvm/gems/ruby-1.9.2-head@rails3/gems/
activerecord-3.0.0.beta4/lib/active_record/persistence.rb:186:in
`create_or_update'
from /opt/rvm/gems/ruby-1.9.2-head@rails3/gems/
activerecord-3.0.0.beta4/lib/active_record/callbacks.rb:290:in `block
in create_or_update'
from /opt/rvm/gems/ruby-1.9.2-head@rails3/gems/
activesupport-3.0.0.beta4/lib/active_support/callbacks.rb:422:in
`_run_save_callbacks'
from /opt/rvm/gems/ruby-1.9.2-head@rails3/gems/
activerecord-3.0.0.beta4/lib/active_record/callbacks.rb:290:in
`create_or_update'
from /opt/rvm/gems/ruby-1.9.2-head@rails3/gems/
activerecord-3.0.0.beta4/lib/active_record/persistence.rb:36:in `save'
from /opt/rvm/gems/ruby-1.9.2-head@rails3/gems/
activerecord-3.0.0.beta4/lib/active_record/validations.rb:40:in `save'
from /opt/rvm/gems/ruby-1.9.2-head@rails3/gems/
activerecord-3.0.0.beta4/lib/active_record/attribute_methods/dirty.rb:
21:in `save'
from /opt/rvm/gems/ruby-1.9.2-head@rails3/gems/
activerecord-3.0.0.beta4/lib/active_record/transactions.rb:235:in
`block (2 levels) in save'
from /opt/rvm/gems/ruby-1.9.2-head@rails3/gems/
activerecord-3.0.0.beta4/lib/active_record/transactions.rb:287:in
`block in with_transaction_returning_status'
from /opt/rvm/gems/ruby-1.9.2-head@rails3/gems/
activerecord-3.0.0.beta4/lib/active_record/connection_adapters/
abstract/database_statements.rb:139:in `transaction'
from /opt/rvm/gems/ruby-1.9.2-head@rails3/gems/
activerecord-3.0.0.beta4/lib/active_record/transactions.rb:202:in
`transaction'
from /opt/rvm/gems/ruby-1.9.2-head@rails3/gems/
activerecord-3.0.0.beta4/lib/active_record/transactions.rb:285:in
`with_transaction_returning_status'
from /opt/rvm/gems/ruby-1.9.2-head@rails3/gems/
activerecord-3.0.0.beta4/lib/active_record/transactions.rb:235:in
`block in save'
from /opt/rvm/gems/ruby-1.9.2-head@rails3/gems/
activerecord-3.0.0.beta4/lib/active_record/transactions.rb:246:in
`rollback_active_record_state!'
from /opt/rvm/gems/ruby-1.9.2-head@rails3/gems/
activerecord-3.0.0.beta4/lib/active_record/transactions.rb:234:in
`save'
from /opt/rvm/gems/ruby-1.9.2-head@rails3/gems/
activerecord-3.0.0.beta4/lib/active_record/base.rb:462:in `create'
from tools/amazon.rb:111:in `block in make_towels'
from tools/amazon.rb:109:in `upto'
from tools/amazon.rb:109:in `make_towels'
from tools/amazon.rb:326:in `loadcats'
from tools/amazon.rb:340:in `<top (required)>'
from /opt/rvm/gems/ruby-1.9.2-head@rails3/gems/railties-3.0.0.beta4/
lib/rails/commands/runner.rb:47:in `eval'
from /opt/rvm/gems/ruby-1.9.2-head@rails3/gems/railties-3.0.0.beta4/
lib/rails/commands/runner.rb:47:in `<top (required)>'
from /opt/rvm/gems/ruby-1.9.2-head@rails3/gems/railties-3.0.0.beta4/
lib/rails/commands.rb:39:in `require'
from /opt/rvm/gems/ruby-1.9.2-head@rails3/gems/railties-3.0.0.beta4/
lib/rails/commands.rb:39:in `<top (required)>'
from script/rails:6:in `require'
from script/rails:6:in `<main>'

[Raimonds Simanovskis]

I tried to reproduce the issue with this example script but couldn't
https://gist.github.com/dc5b6ff5b398b02e90df
Can you try to test with it on your database?

Based on error message it means that there are too many unique SQL
statements created which each requires cursor at database side.
What your database.yml looks like? Haven't you changed cursor_sharing
option?
And is only Rails app using this database? Maybe some other
application is causing shortage of available cursors?
Can you ask your Oracle DBA to take a look on open cursors at database
side to identify what SQL statements are creating these cursors?

Raimonds