ORA-01000: maximum open cursors exceeded

[terry-xiaoyu]

Hi, I am asking for your help because I got the error ORA-01000: maximum open cursors exceeded after calling jamdb_oracle:sql_query/2 many times.

I found there's a same issue that has been marked as fixed: https://github.com/erlangbureau/jamdb_oracle/issues/16, but the same thing is reproduced on my side. Did I missed anything? I am using tag 0.4.9.

I tried to send handle_req(pig, State, {tran, ?TTI_PING}) to oracle, it works sometimes but not always. See: code here, I still don't understand why it is a TTI_PING as the function means to send a reset.

And where can I get the docs about how the oracle packets are encoded (the packet types, packet formats, etc)? I've searched for a long time but got nothing... I want to know how to clear the opened cursors in the oracle database.

[vstavskyi]

Hi,

I can't remember why :)

You're right, handle_req(tran, State, ?TTI_PING) is for ping. https://github.com/erlangbureau/jamdb_oracle/blob/master/src/jamdb_oracle_conn.erl#L120

TNS uses a proprietary protocol. python-oracledb is open source. #112

[terry-xiaoyu]

It seems that the open cursors are not cleared if the oracle returns an error rather than normal response.

See the code here, in this case the cursors are not stored so it won't be cleared later.

I fixed the issue in our repo, but I don't know if it is good to port the code here as I also changed something else, like using erlang dictionary instead of simple process to store the cursors, etc.

[vstavskyi]

About get_result(Cursors)

All queries are prepared statements, driver stores in memory data about 128 prepared statements by their crc32 https://github.com/erlangbureau/jamdb_oracle/blob/master/src/jamdb_oracle_conn.erl#L328

Creation of a process to store data https://github.com/erlangbureau/jamdb_oracle/blob/master/src/jamdb_oracle_conn.erl#L169

Generating crc32 and checking of new or already stored statement https://github.com/erlangbureau/jamdb_oracle/blob/master/src/jamdb_oracle_conn.erl#L247

Storing data of new statements or reset (delete all stored data) if the maximum of 128 was reached https://github.com/erlangbureau/jamdb_oracle/blob/master/src/jamdb_oracle_conn.erl#L284

So, ORA-01000 has no connection with Cursors.

[terry-xiaoyu]

Hi @vstavskyi, thanks for your explanation! I learned that by digging into the code yesterday.

I meant that I believe the problem is cause by L290, because it doesn't store statements as what the L284 does.

So if the oracle returns an error (such as "ORA-01653: unable to extend table SYSTEM.T_MQTT_MSG by 1024 in tablespace SYSTEM"), the open cursors are leaking.

Another question is, I wonder if the L250 and L251 will work because the Cursor is always equal to 0, as the CRC32 sum is newly created.

[vstavskyi]

The idea of L290 was: on error reset all stored data. If all is ok (L284 ) then continue.

I think new module jamdb_oracle_buffer is not needed. You can use direct calls: erlang:get, erlang:put, erlang:erase

[vstavskyi]

Maybe condition on autocommit option in L332 is the problem. https://github.com/erlangbureau/jamdb_oracle/blob/master/src/jamdb_oracle_conn.erl#L332

Internal command CURRESET was added. It's like your reset_cursors. https://github.com/erlangbureau/jamdb_oracle/blob/stage/src/jamdb_oracle_conn.erl#L124