CTX_DDL.SYNC_INDEX Fails With DRG-50857: oracle error in drekalc, ORA-01426: numeric overflow (Doc ID 2609570.1)

 Output from CTX_INDEX_ERRORS in the Text Health Check report shows:

**********************************************************************

Ten (10) most recent text index errors (ctx_index_errors):

**********************************************************************

Fri Sep 27 11:38:36 2019

.. Index name: CTXTEST.MYINDEX Rowid: ON COMMIT SYNC

.. Error:

DRG-50857: oracle error in drekalc

ORA-01426: numeric overflow

<snip>

Manually sync'ing the index reproduces the errors:

SQL> 

exec ctx_ddl.sync_index('ctxtest.myindex');

BEGIN ctx_ddl.sync_index('ctxtest.myindex'); END;

*

ERROR at line 1:

ORA-20000: Oracle Text error:

DRG-50857: oracle error in drekalc

ORA-01426: numeric overflow

ORA-06512: at "CTXSYS.DRUE", line 160

ORA-06512: at "CTXSYS.CTX_DDL", line 1119

ORA-06512: at line 1

CAUSE

The index's DOCID or IDX_NEXTID has reached or exceeded the limit of 2^32-1 (4294967295):

-- what is the IDX_ID of CTXTEST.MYINDEX?

 SQL>

select idx_id, idx_owner, idx_name, IDX_DOCID_COUNT from ctxsys.ctx_indexes where IDX_ID = 1211;

    IDX_ID IDX_OWNER       IDX_NAME         IDX_DOCID_COUNT

---------- --------------- ---------------- ---------------

      1211 CTXTEST         MYINDEX                 11504752

-- what are the values for DOCID_COUNT, IDX_NEXTID

SQL> select /*+ INDEX(a) */ IDX_DOCID_COUNT, IDX_NEXTID, IDX_STATUS from CTXSYS.DR$INDEX a where IDX_ID = 1211;

IDX_DOCID_COUNT IDX_NEXTID IDX_STATUS

--------------- ---------- ------------

       11504752 4294976747 INDEXED 

SOLUTION

There is a hard restriction of 2^32-1 (4294967295) on DOCID for a partition.

Whenever a new row is inserted and indexed (an update is a delete + insert in Text), the document indexed gets a new DOCID. If thousands of inserts/updates are performed daily, this hard limit will be reached much sooner.

Drop and recreate the index so DOCID, and IDX_NEXTID are reset, or

Scale the table by partitioning such that rows are fairly distributed.  By having multiple partitions, there is less chance of reaching 4294967295 as the hard limit is per partition.