optimization with REBUILD option of a non-partitioned index fails with ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION (Doc ID 1096203.1)

Last updated on FEBRUARY 08, 2017

Applies to:

Oracle Text - Version 11.1.0.7 to 11.2.0.4 [Release 11.1 to 11.2]
Information in this document applies to any platform.

Symptoms

Trying to optimize a Text index in rebuild mode, the following error is faced:

ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION

The command executed to rebuild the index is:

execute CTX_DDL.OPTIMIZE_INDEX('MY_INDEX', 'REBUILD');


After a while the index optimization ends with error:

ERROR at line 1:
ORA-20000: Oracle Text error:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.CTX_DDL", line 630
ORA-06512: at line 1


If now a rebuild index is attempted by running

ALTER INDEX MY_INDEX REBUILD;


the following errors are returned:

ALTER INDEX ITEM_NDX2 REBUILD
*
ERROR at line 1:
ORA-24795: Illegal COMMIT attempt made
ORA-29858: error occurred in the execution of ODCIINDEXALTER routine
ORA-24795: Illegal ROLLBACK attempt made
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 546
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "CTXSYS.DRVDDL", line 1509
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "CTXSYS.DRVDDL", line 1754
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "CTXSYS.DRVDISP", line 102
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "CTXSYS.DRVDISP", line 154
ORA-06510: PL/SQL: unhandled user-defined exception


At this point, re-executing the optimize rebuild returns:

DRG-11110: No index found on $I. OPTIMIZE REBUILD not supported

begin
 ctx_ddl.optimize_index('MY_INDEX','REBUILD');
end;
/

begin
*
ERROR at line 1:
ORA-20000: Oracle Text error:
DRG-11110: No index found on $I. OPTIMIZE REBUILD not supported
ORA-24795: Illegal ROLLBACK attempt made
DRG-50857: oracle error in dreii0fsh
ORA-20000: Oracle Text error:
DRG-51311: DML lock handle previously allocated
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.DRVDML", line 837
ORA-06512: at line 1
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.CTX_DDL", line 630
ORA-06512: at line 2

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms