Home > Database, Oracle > Oracle CTXSYS LEXER issues

Oracle CTXSYS LEXER issues

January 17th, 2011

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

Categories: Database, Oracle Tags: , , ,
Comments are closed.