Problem with index rebuild

I'm trying to use the new index rebuild functionality but without any success. Running:

Code:

alter index test_chemaxon_idx rebuild parameters('upgrade=y');

results in:

Quote:

ORA-29858: error occurred in the execution of ODCIINDEXALTER routine ORA-29532: Java call terminated by uncaught Java exception: java.lang.IllegalArgumentException: Illegal option: upgrade ORA-06512: at "JC5ADMIN.JCHEM_CORE_PKG", line 72 ORA-06512: at "JC5ADMIN.JC_IDXTYPE_IM", line 30

and just running:

Code:

alter index test_chemaxon_idx rebuild;

results (after a delay) in:

Quote:

ORA-29858: error occurred in the execution of ODCIINDEXALTER routine ORA-29532: Java call terminated by uncaught Java exception: oracle.jdbc.driver.OracleSQLException: ORA-00942: table or view does not exist ORA-06512: at "JC5ADMIN.JCHEM_CORE_PKG", line 72 ORA-06512: at "JC5ADMIN.JC_IDXTYPE_IM", line 30

after which I need to restart the jchem server to get anything to work.

This is a documentation bug. There is no upgrade option. This will be fixed in JChem version 5.1.3. Sorry for the inconvenience.





But with JChem 5.1.1, the ALTER INDEX REBUILD command without parameters appears to work even on an index that has become invalid after an aborted REBUILD command with the upgrade parameter.





Which JChem version are you using? (Based on the call stack of the error message you got, your version is not 5.1.1.)

I'm running JChem 5.1.2 - just upgraded today (removing the old version & reinstalling).

Did you use ALTER INDEX REBUILD as part of the upgrade?

No. I dropped all the JChem indices and completely removed the JChem user from oracle, then installed the new version from scratch (following the current install instructions from the website). I've then created a new index and finally tried a rebuild (as part of the test for our regular data update process) which is where it failed.

ALTER INDEX REBUILD without parameters works for me with JChem 5.1.2 as well.





Please, could you post the Java stack trace from the Oracle session logs corresponding to this error:

Code:

oracle.jdbc.driver.OracleSQLException: ORA-00942: table or view

Thanks


Peter

Here's the corresponding stack trace:

Code:

oracle.jdbc.driver.OracleSQLException: ORA-00942: table or view does not exist         at oracle.jdbc.driver.T2SConnection.check_error(T2SConnection.java)         at oracle.jdbc.driver.T2SPreparedStatement.checkError(T2SPreparedStatement.java)         at oracle.jdbc.driver.T2SPreparedStatement.executeForRows(T2SPreparedStatement.java)         at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java)         at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java)         at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java)         at chemaxon.jchem.cartridge.JFunctions.incrementUpdateCounter(JFunctions.java:2344)         at chemaxon.jchem.cartridge.Indexing.indexRebuild(Indexing.java:834)         at chemaxon.jchem.cartridge.Indexing.indexAlter(Indexing.java:701)

I can reproduce the problem, if the index is created using a jchemproperties table other than the default. (This is a bug and will be fixed in JChem 5.1.3. Thank you for making me aware of it!)





Is this the case with this particular index? Has test_chemaxon_idx been created using a non-default jchemproperties table?





Thanks


Peter

pkovacs wrote:

Is this the case with this particular index? Has test_chemaxon_idx been created using a non-default jchemproperties table?

Yes. We're pointing the indexes from the separate accounts to the same jchemproperties table (with the JChem oracle user account). This is (was?) necessary because we're not using the set_password function, so all access by the server is to the JChem owner account.





I'll have a play around with synonyms or separate jchemproperties tables and see whether I can get it all working okay with this version.

RobinHill wrote:

I'll have a play around with synonyms or separate jchemproperties tables and see whether I can get it all working okay with this version.

Works fine with a separate jchemproperties table now - I think we had problems pre version 5 so it was probably due to the registration info needing to be shared across schema.





Many thanks for your help.