Oracle Text – Changing SYNC to MANUAL not working always

oracle

Oracle version is 11g. When the Oracle Text index is created, SYNC option is given as "ON COMMIT". Before inserting large number of records, SYNC is changed to "Manual" for performance reason. Below query is run using CallableStatement for this purpose

{call CTX_DDL.REPLACE_INDEX_METADATA('IXFTS_ABC_TABLE','REPLACE METADATA SYNC (MANUAL)')}

In some environments this works fine but in production environments the SYNC option is still "ON COMMIT". No error is thrown when this is not updated. The index is not a partitioned index. Any pointers to troubleshoot further will be helpful.

[Update] I could reproduce this in my local machine rarely. Looks like running the same sql 2 or 3 times is working. Not sure why this fails the first time.

Best Answer

Try this alter option.

alter index IXFTS_ABC_TABLE parameters ('REPLACE METADATA SYNC(Manual)');
Related Question