[Robert Varga]

I created an ER diagram from an Oracle Schema…. and moved around in the database… (the schema has around 1600 tables)…

Now when I look back into the log, I see plenty of the following exceptions:

First there is the following, actually a large number of the following:

!ENTRY com.genuitec.eclipse.sqlexplorer 4 4 Jan 28, 2005 18:36:50.202
!MESSAGE Could not retrieve index data.
!STACK 0
java.sql.SQLException: ORA-01031: insufficient privileges

    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
    at oracle.jdbc.driver.T2CConnection.checkError(T2CConnection.java:630)
    at oracle.jdbc.driver.T2CStatement.execute_for_describe(T2CStatement.java:841)
    at oracle.jdbc.driver.T2CStatement.execute_for_rows(T2CStatement.java:1002)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1028)
    at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1451)
    at oracle.jdbc.OracleDatabaseMetaData.getIndexInfo(OracleDatabaseMetaData.java:3271)
    at net.sourceforge.squirrel_sql.fw.sql.SQLDatabaseMetaData.getIndexInfo(SQLDatabaseMetaData.java:888)
    at com.genuitec.eclipse.sqlexplorer.model.TableNode.getIndexes(Unknown Source)
    at com.genuitec.eclipse.erdesigner.model.ERTableDetail.�(Unknown Source)
    at com.genuitec.eclipse.erdesigner.model.ERTableDetail.<init>(Unknown Source)
    at com.genuitec.eclipse.erdesigner.model.ERTableNode.<init>(Unknown Source)
    at com.genuitec.eclipse.sqlexplorer.actions.CreateERD$A.run(Unknown Source)
    at org.eclipse.core.internal.jobs.Worker.run(Worker.java:66)

This is probably normal, if we don’t have permission to look at some stuff in the DB.

However, after those exceptions (which means after I first get hundreds of the previous one, probably one for each table, as long as there are open cursors), I see the following two exceptions alternating in the log):

!ENTRY com.genuitec.eclipse.sqlexplorer 4 4 Jan 28, 2005 18:36:50.280
!MESSAGE Could not retrieve index data.
!STACK 0
java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1
ORA-01000: maximum open cursors exceeded

    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
    at oracle.jdbc.driver.T2CConnection.checkError(T2CConnection.java:630)
    at oracle.jdbc.driver.T2CStatement.execute_for_describe(T2CStatement.java:841)
    at oracle.jdbc.driver.T2CStatement.execute_for_rows(T2CStatement.java:1002)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1028)
    at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1451)
    at oracle.jdbc.OracleDatabaseMetaData.getIndexInfo(OracleDatabaseMetaData.java:3271)
    at net.sourceforge.squirrel_sql.fw.sql.SQLDatabaseMetaData.getIndexInfo(SQLDatabaseMetaData.java:888)
    at com.genuitec.eclipse.sqlexplorer.model.TableNode.getIndexes(Unknown Source)
    at com.genuitec.eclipse.erdesigner.model.ERTableDetail.�(Unknown Source)
    at com.genuitec.eclipse.erdesigner.model.ERTableDetail.<init>(Unknown Source)
    at com.genuitec.eclipse.erdesigner.model.ERTableNode.<init>(Unknown Source)
    at com.genuitec.eclipse.sqlexplorer.actions.CreateERD$A.run(Unknown Source)
    at org.eclipse.core.internal.jobs.Worker.run(Worker.java:66)



!ENTRY com.genuitec.eclipse.sqlexplorer 4 4 Jan 28, 2005 18:36:50.295
!MESSAGE Could not retrieve foreign key data.
!STACK 0
java.sql.SQLException: ORA-01000: maximum open cursors exceeded

    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
    at oracle.jdbc.driver.T2CConnection.checkError(T2CConnection.java:630)
    at oracle.jdbc.driver.T2CPreparedStatement.execute_for_describe(T2CPreparedStatement.java:851)
    at oracle.jdbc.driver.OracleStatement.execute_maybe_describe(OracleStatement.java:896)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:986)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2888)
    at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:2929)
    at oracle.jdbc.OracleDatabaseMetaData.keys_query(OracleDatabaseMetaData.java:2899)
    at oracle.jdbc.OracleDatabaseMetaData.getImportedKeys(OracleDatabaseMetaData.java:2962)
    at net.sourceforge.squirrel_sql.fw.sql.SQLDatabaseMetaData.getImportedKeys(SQLDatabaseMetaData.java:808)
    at com.genuitec.eclipse.sqlexplorer.model.TableNode.getFK(Unknown Source)
    at com.genuitec.eclipse.erdesigner.model.ERTableDetail.�(Unknown Source)
    at com.genuitec.eclipse.erdesigner.model.ERTableDetail.<init>(Unknown Source)
    at com.genuitec.eclipse.erdesigner.model.ERTableNode.<init>(Unknown Source)
    at com.genuitec.eclipse.sqlexplorer.actions.CreateERD$A.run(Unknown Source)
    at org.eclipse.core.internal.jobs.Worker.run(Worker.java:66)

I guess, this is because of cursors remaining open after the statement throwing the first type of exception bailing out (because of the exception).

So probably the code which throwed the first type (first of the 3) of exception does not clean up after itself in case of an exception (this is what finally blocks are for), and all allowed open cursors were consumed, therefore all subsequent tries were throwing the second and the third exception (which is probably either two operations tried to be carried out after each other, both failing because of no further cursors can be allocated, or an exception logged twice).

All in all, please check your code, so that when you create a statement object or a result set, you really do close it in the finally clause.

Regards,

Robert Varga

[Riyad Kalla]

Rob thank you for bringing this to our attention, I have send you post off to one of the devs of that module.

[Brian Fernandes]

Rob,

We are aware of that issue and recently increased the exception handling drastically to include finally blocks where the result sets ARE closed.

I’m checking your stack trace right now to see if we missed anything.

Thanks for pointing it out,
Brian.

[Brian Fernandes]

Rob, I had a look at your last post (with the feature list) – you hadn’t mentioned what version of ME you were using.
Are you using 3.8.3 or 3.8.4?

Brian.

[Robert Varga]

It is 3.8.4 (I think, I did this after the upgrade :-))

Regards,

Robert

[Riyad Kalla]

Rob,
Can you give us the BuildID from the Window > Prefs > MyEclispe page, and then shut down, erase your log, -clean and restart MyEclipse then try to do the DB stuff again and see if it freaks out? The reason I ask is because we are pretty sure we nailed everything (From the stack traces) and are having a hard time narrowing down what might be wrong. One of the developers wanted to simply ban you from posting, but after a long talk we realized that wasn’t the best strategy 😀

[Robert Varga]

Ok, I try to do this on Monday…

If that developer persists in trying to ban me, I will send him the entire log to read…. it will take a while until he gets through it, and meanwhile I can find out some other things to delay him further… 🙂

The build id actually should be the same which is available as an archived update site, I downloaded that on Friday evening (it is already Saturday here :-), and updated my 3.8.3 to it.

Regards,

Robert

[Brian Fernandes]

Developer? ban? who? me? you? ban?
*Brian looks around…
😉

Rob, all exceptions relating to result sets (esp those in the stack trace you pasted) are logged.
So, if you clean your log and try again (and subsequently paste / send me the log), we can get to know exactly what’s going wrong.

Regards,
Brian.

[Robert Varga]

@Support-Brian wrote:

Developer? ban? who? me? you? ban?
*Brian looks around…
😉

Rob, all exceptions relating to result sets (esp those in the stack trace you pasted) are logged.
So, if you clean your log and try again (and subsequently paste / send me the log), we can get to know exactly what’s going wrong.

Regards,
Brian.

Brian,

This does not seem to be a result-set problem.

This seems to be a statement problem. Look at the first exception, since that is causing the problem, that is depleting the cursor pool.

The second and the third are just a result of no more open cursors.

The first exception is coming from the following code:

!ENTRY com.genuitec.eclipse.sqlexplorer 4 4 Jan 28, 2005 18:36:50.202
!MESSAGE Could not retrieve index data.
!STACK 0
java.sql.SQLException: ORA-01031: insufficient privileges

   at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
   at oracle.jdbc.driver.T2CConnection.checkError(T2CConnection.java:630)
   at oracle.jdbc.driver.T2CStatement.execute_for_describe(T2CStatement.java:841)
   at oracle.jdbc.driver.T2CStatement.execute_for_rows(T2CStatement.java:1002)
   at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1028)
   at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1451)
   at oracle.jdbc.OracleDatabaseMetaData.getIndexInfo(OracleDatabaseMetaData.java:3271)
   at net.sourceforge.squirrel_sql.fw.sql.SQLDatabaseMetaData.getIndexInfo(SQLDatabaseMetaData.java:888)
   at com.genuitec.eclipse.sqlexplorer.model.TableNode.getIndexes(Unknown Source)
   at com.genuitec.eclipse.erdesigner.model.ERTableDetail.�(Unknown Source)
   at com.genuitec.eclipse.erdesigner.model.ERTableDetail.<init>(Unknown Source)
   at com.genuitec.eclipse.erdesigner.model.ERTableNode.<init>(Unknown Source)
   at com.genuitec.eclipse.sqlexplorer.actions.CreateERD$A.run(Unknown Source)
   at org.eclipse.core.internal.jobs.Worker.run(Worker.java:66)
   ...

This is, as far as I see, a statement problem: you execute a statement, and get an ORA-1031 exception instead of a result set, and most probably the statement is not cleaned up, not the result set, since you don’t even have a result set to manipulate, so you might have been looking at the wrong place.

Of course the actual problem might be in Squirrel, I don’t know it at all, still it needs to be fixed, I think.

Regards,

Robert

[Brian Fernandes]

Rob,

Excellent point about the open statements. I missed that in your first post. The problem does seem to be in squirrel, but nevertheless it definitely needs to be fixed.

We’re investigating further now and will let you know what turns up.
Thanks,
Brian.

[support-jeff]

Rob –

Please try the following:
* Edit your connection profile, to require the password prompt when connecting
* when connecting, uncheck the auto-commit option

Now retry your operations. Do they work now? Auto-commit is a notorious cursor hog – although I have not seen the same behavior from Oracle.

[support-jeff]

BTW – our code does keep a physical connection to the database while you have a connection profile “connected”. However, we are not creating any statements or retrieving result sets – this all happens inside the OracleDatabaseMetaData implementation class. You might try using the debug version of the driver to see if it sheds more light on the problem?

[Robert Varga]

@support-jeff wrote:

BTW – our code does keep a physical connection to the database while you have a connection profile “connected”. However, we are not creating any statements or retrieving result sets – this all happens inside the OracleDatabaseMetaData implementation class. You might try using the debug version of the driver to see if it sheds more light on the problem?

Can you please post your version of SquirrelSQL so that I may look into it, to see if the error is there?

Regards,

Robert

ps. I will do the tests you suggested in the evening, I would not like to possibly paralyze the database which other people are using in the afternoon…

[Robert Varga]

@support-jeff wrote:

Rob –

Please try the following:
* Edit your connection profile, to require the password prompt when connecting
* when connecting, uncheck the auto-commit option

Now retry your operations. Do they work now? Auto-commit is a notorious cursor hog – although I have not seen the same behavior from Oracle.

This does not change anything… not that I expected it to…

Regards,

Robert

[Robert Varga]

@support-jeff wrote:

BTW – our code does keep a physical connection to the database while you have a connection profile “connected”. However, we are not creating any statements or retrieving result sets – this all happens inside the OracleDatabaseMetaData implementation class. You might try using the debug version of the driver to see if it sheds more light on the problem?

Hm, I see your problem, guys… the Oracle driver itself is leaking the cursor, as far as I can see… of course only in case the metadata itself cannot somehow be made to close its connections.

As I see, Squirrel makes an invocation to Connection.getMetadata() every time getIndexInfo is invoked on it. That probably yields a new DatabaseMetadata object every time it is invoked (with the leaked cursor after exceptions).

What you could try in this case would be to change your code not to use Squirrel’s implementation, but make an implementation which caches DatabaseMetadata. This approach would probably close the previous leaking OracleStatement.

I will try to make a test case for this to see…

Actually it would probably be faster, too, not getting a new metadata every time…

Of course the proper solution would be if the Oracle driver would be fixed so that it does not leak statements…

Regards,

Robert

[Author]

Posts