1 in 7 insert statements failing.

User 7f33ec9a5c

17-10-2012 00:00:41

Hi,


I need to get some testing done, so I'm working with our existing JChem table,  which has the messy custom properties table:





exec jchem_table_pkg.create_jctable('structure_jc1', 'jchem.jc_idx_property', 16, 2, 6, 'jc_structure_id number(15)', null, 1, 'tableType:molecules');


 
     CREATE INDEX jc_idx ON structure_jc1(cd_structure) INDEXTYPE IS jchem.JC_IDXTYPE parameters('JChemPropertiesTable=jchem.jc_idx_property')


When inserting to that table, 1 in 7 insert statements fails with an error.


I'm inserting in pl/sql with:



a:= jchem.jchem_table_pkg.jc_insert(sSMI,'structure_jc1', 'jchem.jc_idx_property', 'true', 'false', 'haltOnBadFormat:y');


 


Yet 1 in 7 inserts fails with an error.


The most common error is:


ORA-29532: Java call terminated by uncaught Java exception: java.sql.SQLException: ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE


 for [O-]C1=C([N+]([O-])=O)C([O-])=C([N+]([O-])=O)C=C1[N+]([O-])=O.[Pb+2]


 [Failed statement: `INSERT INTO SENOBASE.structure_jc1 (cd_id, cd_structure, cd_smiles, cd_formula, cd_molweight, cd_hash, cd_flags, cd_timestamp, cd_fp1, cd_fp2, cd_fp3, cd_fp4, cd_fp5, cd_fp6, cd_fp7, cd_fp8, cd_fp9, cd_fp10, cd_fp11, cd_fp12, cd_fp13, cd_fp14, cd_fp15, cd_fp16)  VALUES(?, e          



 


I'm currrently at 84907 successful inserts and about 19000 failures, after trying around 103907 structures.


 


What would cause the index to be unsable only some of the time?

ChemAxon aa7c50abf8

17-10-2012 18:05:27

I don't know why the failure is intermittent, but chances are that we don't have to know it, we just need to get rid of the bad indexe(s) (drop them and recreate those of them which you know are needed):


select status, domidx_opstatus, index_name from dba_indexes where ityp_name = 'JC_IDXPROPERTY';

Peter