ALTER INDEX REBUILD - ORA-39726: add/drop column on compress

Hi

We use JChem 5.6.0.4 on Oracle 11.2.0.3.

Whe I've executed: ALTER INDEX .... REBUIL I've got error:

Error report:


SQL Error: ORA-29858: error occurred in the execution of ODCIINDEXALTER routine


ORA-29532: Java call terminated by uncaught Java exception: java.rmi.ServerException: RemoteException occurred in server thread; nested exception is: 


	java.rmi.RemoteException: ORA-39726: unsupported add/drop column operation on compressed tables


ORA-06512: at "JCHEM_TESTER.JCHEM_CORE_PKG", line 105


ORA-06512: at "JCHEM_TESTER.JC_IDXTYPE_IM", line 38


29858. 00000 -  "error occurred in the execution of ODCIINDEXALTER routine"


*Cause:    Failed to successfully execute the ODCIIndexAlter routine.


*Action:   Check to see if the routine has been coded correctly.

For such cases (for compressed tables) you have to use DDL set unused column instead of drop column.

For example,

ALTER TABLE t_i_jcx SET UNUSED COLUMN cd_formula;

instead of

ALTER TABLE t_i_jcx DROP COLUMN cd_formula; 

 

Best regards...

Pavel

Hi Pavel,

Thank you for reporting this problem and for the suggested solution! We will implement it as soon as possible.

Peter

pkovacs wrote:

Hi Pavel, Thank you for reporting this problem and for the suggested solution! We will implement it as soon as possible. Peter

You are welcome :)

Also you can get info about compression state of table from oracle dictionary (user_tables, all_tables, dba_tables) - there is a column COMPRESSION. This column has values ENABLED and DISABLED.

Pavel

Many thanks, Pavel!

I now feel fully equipped to tacke this case.

Peter

Pavel,

This problem is planned to be fixed in JChem version 5.8. In the meantime, a preview release (based on production JChem version 5.7.0) with the JChem Cartridge fixes and improvements planned for 5.8.0 is available here: https://shared.chemaxon.com/projects/jcc/jchem-jcc-5.8preview-on-5.7.zip . Please, have a look when/if you have time.

Thanks

Peter

pkovacs wrote:

Pavel, This problem is planned to be fixed in JChem version 5.8. In the meantime, a preview release (based on production JChem version 5.7.0) with the JChem Cartridge fixes and improvements planned for 5.8.0 is available here: https://shared.chemaxon.com/projects/jcc/jchem-jcc-5.8preview-on-5.7.zip . Please, have a look when/if you have time. Thanks Peter

Thanks a lot.

I'll look at it on Monday (or maybe on Sunday).

Pavel

Hi Pavel,

JChem 5.8.0 has been released with the fix for this problem.

Peter