Oracle CTXSYS LEXER issues
Hello,
I recently tried to troubleshoot an application issue that was related to a text index. The index itself appeared in valid state but there was obviously some kind of a problem with it.
I started with the following
SQL> connect sys as sysdba
Enter password:
Connected.
SQL> DROP INDEX USER5.INDX71;
Index dropped.
SQL>CREATE INDEX USER5.INDX71 ON USER5.APP_NOTES (DETAIL_TEXT) INDEXTYPE IS CTXSYS.CONTEXT;
REATE INDEX USER5.INDX71 ON USER5.APP_NOTES (DETAIL_TEXT) INDEXTYPE IS CTXSYS.CONTEXT;
*
ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-10700: preference does not exist: CTXSYS.DEFAULT_LEXER
ORA-06512: at “CTXSYS.DRUE”, line 160
ORA-06512: at “CTXSYS.TEXTINDEXMETHODS”, line 364
There was something wrong with the CTXSYS schema and especially with the DEFAULT_LEXER.
I fixed this by reapplying the correct drdefXX.sql script for the database locale.
[oracle@node01 defaults]$ ls
dr0defdp.sql drdefeg.sql drdefhu.sql drdefnl.sql drdefs.sql
dr0defin.sql drdefel.sql drdefin.sql drdefn.sql drdefth.sql
drdefar.sql drdefesa.sql drdefi.sql drdefpl.sql drdeftr.sql
drdefbg.sql drdefesm.sql drdefis.sql drdefptb.sql drdefuk.sql
drdefbn.sql drdefe.sql drdefiw.sql drdefpt.sql drdefus.sql
drdefca.sql drdefet.sql drdefja.sql drdefro.sql drdefvn.sql
drdefcs.sql drdeffrc.sql drdefko.sql drdefru.sql drdefzhs.sql
drdefdin.sql drdeff.sql drdeflt.sql drdefsf.sql drdefzht.sql
drdefdk.sql drdefgb.sql drdeflv.sql drdefsk.sql
drdefd.sql drdefhr.sql drdefms.sql drdefsl.sql
[oracle@node01 defaults]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 – Production on Tue Jan 11 14:18:23 2011
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, Real Application Clusters, Oracle Label Security, Data Mining
and Real Application Testing options
SQL> connect ctxsys
Enter password:
Connected.
SQL> @drdefptb.sql
Creating lexer preference…
PL/SQL procedure successfully completed.
Creating wordlist preference…
PL/SQL procedure successfully completed.
Creating stoplist…
PL/SQL procedure successfully completed.
Creating default policy…
PL/SQL procedure successfully completed.
SQL> connect sys as sysdba
Enter password:
Connected.
SQL> DROP INDEX USER5.INDX71;
Index dropped.
SQL>CREATE INDEX USER5.INDX71 ON USER5.APP_NOTES (DETAIL_TEXT) INDEXTYPE IS CTXSYS.CONTEXT;
Index created.
This fixed the INDEX and resolved the application issue.
HTH,
St0ma