[arunpers]

Hello,
I am trying MyEclipse. Looks like an impressive collection of plugins.

I was excited to see the DB features you had. I tried the Create ER Diagram option to create a ER diagram of an existing database. When I did this on an oracle instance that had a few tables, it generated a good ER diagram. However, when i tried the same for another oracle instance with close to 400 tables, the er diagram simply lists the tables, with no lines indicating relations between them. Is there a limitation on the number of tables that the plugin can handle or is it some other problem that I need to look at? (Also, only the table names are displayed, the columns are not displayed)

Regards.

[Brian Fernandes]

arunpers,

There is no limitation on the number of tables that the ER Designer can display. We have tested the ER Designer on schemas with more than 500 tables.

You weren’t seeing any relationships because only the table names were obtained from the database, the ER Designer didn’t have enough information to draw the relationship lines.

Are you able to see the complete schema structure for the concerned schema in the DB Explorer? Can you see the Tables and their column information?

You mention another oracle instance. Is this another installation of Oracle (vs. the one where the ER Designer worked correctly), perhaps using another driver for connection?

Could you please mention what version of Oracle you are using and what driver you are using to connect to the database?

This will help us determine where the problem lies,
Thanks,
Brian.

[arunpers]

Are you able to see the complete schema structure for the concerned schema in the DB Explorer? Can you see the Tables and their column information? – Yes, the dbexplorer displays the tables and columns fine.

You mention another oracle instance. Is this another installation of Oracle (vs. the one where the ER Designer worked correctly), perhaps using another driver for connection? – The instance where it worked is another installation of oracle, but I used the same driver.

The version of oracle where I was not able to see the relations is:
Oracle9i Enterprise Edition Release 9.2.0.1.0

The version of oracle where I see the relations is:
Oracle8i Enterprise Edition Release 8.1.7.0.0

I used the oralce thin driver ojdbc14.jar that was installed when i installed oracle 9 client on my machine. I used the same for connecting to both the installations. Do you think I should get a different driver? Interesting that the driver is for 9 and it works against the version 8 database.
Any suggestions will be greatly appreciated..

Regards.

[Brian Fernandes]

arunpers,

We would have expected to see the problem with the Oracle 8 database instead of 9 🙂

ATM, I can’t really discern what the problem is – so I need you to…

1) I doubt the problem is due to the size of your schema. Nevertheless, could you try creating an ER diagram with another schema in Oracle 9i? Maybe you could create a small dummy schema for testing.
2) When you see the columns of the schema in the DB Explorer, and in the Table Info view, is all the information present? i.e. Primary Keys, Foreign Keys, Indices? Or is some information missing?
3)Can you examine the error log for related errors? If you’re finding it difficult to locate them-I would suggest deleting the log before starting eclipse and then trying.
4)Try refreshing the Diagram from the View menu after you have created the diagram.

Thank you for your patience, I hope this information helps zer0ing in on the issue,

Brian.

[arunpers]

Thanks Brian,

1. I did create a 2 table schema on the oracle 9 instance and was able to create the ER diagram and it displays the relations fine.

2. I do see the following errors when I go the table info view.

Error activating index detail view
java.sql.SQLException: ORA-01000: maximum open cursors exceeded

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)

at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)

at oracle.jdbc.ttc7.Oopen.receive(Oopen.java:120)

at oracle.jdbc.ttc7.TTC7Protocol.open(TTC7Protocol.java:586)

at oracle.jdbc.driver.OracleStatement.<init>(OracleStatement.java:385)

at oracle.jdbc.driver.OracleConnection.privateCreateStatement(OracleConnection.java:772)

at oracle.jdbc.driver.OracleConnection.createStatement(OracleConnection.java:712)

at oracle.jdbc.OracleDatabaseMetaData.getIndexInfo(OracleDatabaseMetaData.java:3661)

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.sqlexplorer.views.details.IndexDetail$1.run(Unknown Source)

at org.eclipse.swt.custom.BusyIndicator.showWhile(BusyIndicator.java:69)

at com.genuitec.eclipse.sqlexplorer.views.details.IndexDetail.activate(Unknown Source)

at com.genuitec.eclipse.sqlexplorer.views.DetailManager$1.widgetSelected(Unknown Source)

at org.eclipse.swt.widgets.TypedListener.handleEvent(TypedListener.java:89)

at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:82)

at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:796)

at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:2772)

at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:2431)

at org.eclipse.ui.internal.Workbench.runEventLoop(Workbench.java:1377)

at org.eclipse.ui.internal.Workbench.runUI(Workbench.java:1348)

at org.eclipse.ui.internal.Workbench.createAndRunWorkbench(Workbench.java:254)

at org.eclipse.ui.PlatformUI.createAndRunWorkbench(PlatformUI.java:141)

at org.eclipse.ui.internal.ide.IDEApplication.run(IDEApplication.java:96)

at org.eclipse.core.internal.runtime.PlatformActivator$1.run(PlatformActivator.java:335)

at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:273)

at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:129)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

at java.lang.reflect.Method.invoke(Method.java:324)

at org.eclipse.core.launcher.Main.basicRun(Main.java:185)

at org.eclipse.core.launcher.Main.run(Main.java:704)

at org.eclipse.core.launcher.Main.main(Main.java:688)

Error activating columns detail view
java.sql.SQLException: ORA-01000: maximum open cursors exceeded

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)

at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)

at oracle.jdbc.ttc7.Oopen.receive(Oopen.java:120)

at oracle.jdbc.ttc7.TTC7Protocol.open(TTC7Protocol.java:586)

at oracle.jdbc.driver.OracleStatement.<init>(OracleStatement.java:385)

at oracle.jdbc.driver.OracleStatement.<init>(OracleStatement.java:413)

at oracle.jdbc.driver.OraclePreparedStatement.<init>(OraclePreparedStatement.java:119)

at oracle.jdbc.driver.OraclePreparedStatement.<init>(OraclePreparedStatement.java:92)

at oracle.jdbc.driver.OracleConnection.privatePrepareStatement(OracleConnection.java:950)

at oracle.jdbc.driver.OracleConnection.prepareStatement(OracleConnection.java:802)

at oracle.jdbc.OracleDatabaseMetaData.getColumns(OracleDatabaseMetaData.java:2552)

at net.sourceforge.squirrel_sql.fw.sql.SQLDatabaseMetaData.getColumns(SQLDatabaseMetaData.java:940)

at com.genuitec.eclipse.sqlexplorer.model.TableNode.getColumns(Unknown Source)

at com.genuitec.eclipse.sqlexplorer.views.details.ColumnsDetail$3.run(Unknown Source)

at org.eclipse.swt.custom.BusyIndicator.showWhile(BusyIndicator.java:69)

at com.genuitec.eclipse.sqlexplorer.views.details.ColumnsDetail.activate(Unknown Source)

at com.genuitec.eclipse.sqlexplorer.views.DetailManager.activate(Unknown Source)

at com.genuitec.eclipse.sqlexplorer.views.TableInfoView.setInput(Unknown Source)

at com.genuitec.eclipse.sqlexplorer.views.DatabaseTreeViewer$4.selectionChanged(Unknown Source)

at org.eclipse.jface.viewers.Viewer$2.run(Viewer.java:159)

at org.eclipse.core.internal.runtime.InternalPlatform.run(InternalPlatform.java:616)

at org.eclipse.core.runtime.Platform.run(Platform.java:747)

at org.eclipse.jface.viewers.Viewer.fireSelectionChanged(Viewer.java:157)

at org.eclipse.jface.viewers.StructuredViewer.updateSelection(StructuredViewer.java:1394)

at org.eclipse.jface.viewers.StructuredViewer.handleSelect(StructuredViewer.java:693)

at org.eclipse.jface.viewers.StructuredViewer$4.widgetSelected(StructuredViewer.java:718)

at org.eclipse.jface.util.OpenStrategy.fireSelectionEvent(OpenStrategy.java:180)

at org.eclipse.jface.util.OpenStrategy.access$3(OpenStrategy.java:175)

at org.eclipse.jface.util.OpenStrategy$1.handleEvent(OpenStrategy.java:324)

at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:82)

at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:796)

at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:2772)

at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:2431)

at org.eclipse.ui.internal.Workbench.runEventLoop(Workbench.java:1377)

at org.eclipse.ui.internal.Workbench.runUI(Workbench.java:1348)

at org.eclipse.ui.internal.Workbench.createAndRunWorkbench(Workbench.java:254)

at org.eclipse.ui.PlatformUI.createAndRunWorkbench(PlatformUI.java:141)

at org.eclipse.ui.internal.ide.IDEApplication.run(IDEApplication.java:96)

at org.eclipse.core.internal.runtime.PlatformActivator$1.run(PlatformActivator.java:335)

at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:273)

at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:129)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

at java.lang.reflect.Method.invoke(Method.java:324)

at org.eclipse.core.launcher.Main.basicRun(Main.java:185)

at org.eclipse.core.launcher.Main.run(Main.java:704)

at org.eclipse.core.launcher.Main.main(Main.java:688)

😕 😕

Any clues??.

Arun

[Brian Fernandes]

Arun,

Too many cursors open is the problem; when this happens, we cannot retrieve any more metadata from the database and you would see incomplete information.

These questions should help us track it down further
1) Is anything else operating on your Oracle installation besides ME?

2) Can you restart your Oracle server (that should reset the cursors), make sure only ME is accessing your database and open up the table in the Table Info view? Please check the timestamps (so that you know you are looking at the correct logs) of the log records, if any.
– Do you see any information in the Table Info view at all? If so, is any information missing / incomplete?

Please check the logs for any reports even if the Table Info shows everything okay.

3) If the Table Info shows up fine and there are no log errors – then go on to create the ER Diagram.

If it does not show up as expected, try restarting your Oracle server and come to step #3 directly.

Thanks for tracking this down with us,

Brian.

[arunpers]

There are several things operating against the database, a few toad clients and a application are running against it.

Unfortunately, I cannot restart the database. However when I tried looking at the indexes tab in the table info view, instead of the too many cursors I get the following insufficient privileges error. Maybe the database was restarted sometime yesterday and that cleared the too many cursors error.

So now for the insufficient privileges error, is this anything to do with the user I use to connect to the database instance? Does this user need a particular privilege? The user I use to connect via ME does not have admin privileges but has most other privileges, if you require a particular privilege to generate the ER diagram I will be able to determine if i have that or not.

Thanks.

Error message:
java.sql.SQLException: ORA-01031: insufficient privileges

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)

at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)

at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573)

at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891)

at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1093)

at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2047)

at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1940)

at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2709)

at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:796)

at oracle.jdbc.OracleDatabaseMetaData.getIndexInfo(OracleDatabaseMetaData.java:3669)

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.sqlexplorer.views.details.IndexDetail$1.run(Unknown Source)

at org.eclipse.swt.custom.BusyIndicator.showWhile(BusyIndicator.java:69)

at com.genuitec.eclipse.sqlexplorer.views.details.IndexDetail.activate(Unknown Source)

at com.genuitec.eclipse.sqlexplorer.views.DetailManager$1.widgetSelected(Unknown Source)

at org.eclipse.swt.widgets.TypedListener.handleEvent(TypedListener.java:89)

at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:82)

at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:796)

at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:2772)

at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:2431)

at org.eclipse.ui.internal.Workbench.runEventLoop(Workbench.java:1377)

at org.eclipse.ui.internal.Workbench.runUI(Workbench.java:1348)

at org.eclipse.ui.internal.Workbench.createAndRunWorkbench(Workbench.java:254)

at org.eclipse.ui.PlatformUI.createAndRunWorkbench(PlatformUI.java:141)

at org.eclipse.ui.internal.ide.IDEApplication.run(IDEApplication.java:96)

at org.eclipse.core.internal.runtime.PlatformActivator$1.run(PlatformActivator.java:335)

at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:273)

at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:129)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

at java.lang.reflect.Method.invoke(Method.java:324)

at org.eclipse.core.launcher.Main.basicRun(Main.java:185)

at org.eclipse.core.launcher.Main.run(Main.java:704)

at org.eclipse.core.launcher.Main.main(Main.java:688)

[support-jeff]

Glad to hear the open cursor issue has resolved itself; I hate it when things go wrong and myteriously “solve themselves”. Regarding the insufficient privileges, check with your DBAs. This is almost certainly a problem with the privileges assigned to your login id. You basically have to have SELECT privs for some of the system catalogs (USER_INDEXES or something like that in Oracle).

[arunpers]

Interestingly I am able to view the indexes when I am logged in as the same user in TOAD. I will check with my DBA to find what privileges I do not have and post the results.

[james_render50]

I am having the same issue with my oracle 8.1.7 installation. Unfortunately its not possible to log directly into the schema that contains the tables, but rather into another that has synonym pointers, a read schema. When I try to create the ER diagram from this schema, nothing appears. Its possible for me to generate the diagram from the dba schema (containing the tables) but no relationships/table details appear.

There is nothing logged unfortunately.

[Brian Fernandes]

James,

I would like you to answer these questions for both schemas (read schema and dba schema)

1) Can you browse these schemas in the DB Explorer?

2) When you see the columns of the schema in the DB Explorer, and in the Table Info view, is all the information present? i.e. Primary Keys, Foreign Keys, Indices? Or is some information missing?

3) What driver are you using to connect to the database?

Thanks,
Brian.

[arunpers]

The simple from the dba was that I have almost all privileges except things to do with administration.
The tables are not owned by me, they are owned by another db user, however I have all privileges on them.

Also, it is weird because I am able to view the indexes from within toad. It is only with ME that i get the insufficient privileges exception. If you know what specific privilege I should look for I can probably ask the dba specfic answers.

I even run the following sql from oracle sql plus client and get valid results:
select * from all_indexes where table_name = ‘some_table’

Arun N

[Brian Fernandes]

Arun,

You mentioned that you are able to see the table and it’s columns perfectly in the DB Explorer. Currently, when you are experiencing the privileges error you detailed above, can you still see the tables and columns of the concerned schema? Are the PK and FK annotations present where expected?

We really have no clue what is going wrong, it could be an oracle specific problem and we’re not Oracle experts. We can’t really say what privileges you need exactly.
Oracle does have an INDEX privilege, I’m guessing that is only required to create Indices, but I’m not sure.

You mentioned a dummy schema you created for testing on the 9i instance, I assume you can see the Table Info for that schema without any issues? Maybe you could check the difference in the privileges of the two and see what is missing in the problematic schema.

Finally, perhaps someting is dorked with the connection profile; can you re-create the profile with the correct user and recheck? I briefly saw (google) that some users had problems if they used different connect strings which resulted in this error. Recheck your connect string; you current string may give you access, but not *all* the access you need. /Again, I’m not an oracle connect string expert 😉

Do let us know what turns up,
Brian.

[Thomas Benz]

Hello!
Could someone tell me where I can find the “error log” file. I have the same problem with both DB2 and MySQL – The ER-diagram doesen’t show the relationships between the tables.
Thanks
Thomas

[Brian Fernandes]

Thomas you can find the log file in the .metadata folder in your workspace folder.
The file name is “.log”

Please answer these questions as well.

1) Can you browse these schemas in the DB Explorer?

2) When you see the columns of the schema in the DB Explorer, and in the Table Info view, is all the information present? i.e. Primary Keys, Foreign Keys, Indices? Or is some information missing?

3) What driver are you using to connect to the database?

Thanks,
Brian.

[Author]

Posts