ORA-1000 Maximum Open Cursor Exceeded Problem

[J. Beaven]

I'm constantly running into this oracle exception after my app has been running for a couple of hours.

I'm using DOA4.0.6/Oracle10g server/9.2i client/Delphi7 pro

I think the default max per session is 300.
I'm using the following to see how many cursors are open. Is it correct?

select *
from v$sesstat
where statistic#= 3 and sid in (select sid
from v$session
where upper(program) like 'PROJECT%')

I've written a very simple project that uses TOracleSession/TOracleDataset to open a resultset and I can see the cursor count go up. However when I call CloseAll the count does not go down.
In fact it doesn't go down until the session is closed. Is this expected behaviour?

Thanks

Jo

 

[Marco Kalter]

If the open cursor count continuously increases, this may suggest that you are dynamically creating query or dataset instances without freeing them. This can happen directly or indirectly through a datamodule.

Note however that the open cursor count is not immediately updated after closing a query or dataset. It is updated after executing a new query, which makes it difficult to trace these problems.

 

[J. Beaven]

All my datasets are created dynamically, and I'm pretty sure they are all freed appropriately. I will add some logging code to the constructor / destructor (I encapsulate TOracleDataset in my own class) to see if any are not being freed.

Incidentally is there a query I can run on oracle to tell me what the max open cursors is set to?

 

[cdev]

Hi J.Beaven,

Did you use a CloseAll before doing a Free on your TOracleDataSets ?

Help file description for TOracleDataSet.CloseAll procedure says :

CloseAll closes the dataset, including all associated cursors. By default, the cursor of the select statement of the dataset will remain open when you close the dataset, so that it does not need to be parsed again when it is reopened.

 

[cdev]

You can run a select count query on V$OPEN_CURSOR to know how many cursors are opened.
You just need to write appropriate where clause to filter only one session.

 

[J. Beaven]

Thanks for your help.

I wasn't calling CloseAll, and one of my routines was leaking dataset instances.

I fixed those problems and it seems OK now.