How to fix DRG-10599: column is not indexed error | Oracle 18g

I have two oracle databases (Test and production). Both are cloned from the same first database. My query is working fine on the test server. But the production server is giving an error.

SELECT * FROM MY_VIEW_TABLE mliv
WHERE CATSEARCH (TITLE, 'law*', NULL) > 0

This query is returning a response without issue. But the production server is giving an error.

ORA-20000: Oracle Text error: DRG-10599: column is not indexed

I also tried creating an index:

CREATE INDEX LACT_TITLE_TI ON MJ_LACTS(TITLE) 
       INDEXTYPE IS CTXSYS.CONTEXT;

Then I tried running the query again. Anyway, the error above. I also looked at pre-existing indexes. The same.

SELECT *
FROM dba_ind_columns
WHERE TABLE_NAME = 'MJ_LACTS'

Also, i tried these answers too:

• ORA-20000: Oracle Text error: DRG-10599: column is not indexed
• Oracle Text error: DRG-10599: the colum is not indexed
• DRG-10599: Column Is Not Indexed After Running ALTER TABLE .... TRUNCATE PARTITION (Doc ID 1675478.1)

Both servers have the same indexes, both servers have the same version.

UPDATE 1

My table structure:

CREATE TABLE CR.MJ_LACTS (
  LACT_ID                  NUMBER(10, 0)       NOT NULL,
  INIT_LACT_ID             NUMBER(10, 0)       NOT NULL,
  EDITION_NUMBER           NUMBER(2, 0)        DEFAULT 0 NOT NULL,
  LANG_ID                  NUMBER(10, 0)       NOT NULL,
  STATUS_ID                NUMBER(10, 0)       NOT NULL,
  FORM_ID                  NUMBER(10, 0)       NOT NULL,
  TITLE                    VARCHAR2(4000 CHAR) NOT NULL,
  VALIDITY_ID              NUMBER(10, 0)       NOT NULL,
  STATE_ID                 NUMBER(10, 0)       NOT NULL,
  OWNER_ID                 VARCHAR2(30 CHAR)   NOT NULL,
  EFFECTIVE_DATE           DATE                NOT NULL,
  DISPLAY_NAME_FORM        VARCHAR2(4000 CHAR) NOT NULL,
  TOC_YN                   VARCHAR2(1 CHAR)    DEFAULT 'N' NOT NULL,
  MINOR_YN                 VARCHAR2(1 CHAR)    DEFAULT 'N' NOT NULL,
  ACTUALITY_YN             VARCHAR2(3 CHAR)    DEFAULT 'Y' NOT NULL,
  PUBLIC_YN                VARCHAR2(1 CHAR)    DEFAULT 'N' NOT NULL,
  PUBLISHED_YN             VARCHAR2(1 CHAR)    DEFAULT 'N' NOT NULL,
  CONDITIONAL_PUBLIC_YN    VARCHAR2(1 CHAR)    DEFAULT 'N' NOT NULL,
  ACCEPTANCE_DATE_RULE_YN  VARCHAR2(1 CHAR)    DEFAULT 'Y' NOT NULL,
  EFFECTIVE_DATE_RULE_YN   VARCHAR2(1 CHAR)    DEFAULT 'Y' NOT NULL,
  ELEMENT_TRANSITS_RULE_YN VARCHAR2(1 CHAR)    DEFAULT 'Y' NOT NULL,
  ELEMENT_EDITION_RULE_YN  VARCHAR2(1 CHAR)    DEFAULT 'Y' NOT NULL,
  ST_REG_NUMBER            VARCHAR2(8 CHAR),
  ST_REG_DATE              DATE,
  MJ_REG_NUMBER            VARCHAR2(15 CHAR),
  MJ_REG_DATE              DATE,
  OFFICIAL_SRC_ID          NUMBER(10, 0),
  OFFICIAL_SRC_DATE        DATE,
  OFFICIAL_SRC_NUMBER      VARCHAR2(30 CHAR),
  OFFICIAL_SRC_CLAUSE      VARCHAR2(30 CHAR),
  OFFICIAL_SRC_DATE_SGN    DATE,
  OBSOLETE_DATE            DATE,
  CANCEL_EVENT             VARCHAR2(2000 CHAR),
  QUANTITY_PAGES           NUMBER(6, 0),
  ACCEPTANCE_DATE          DATE,
  LACT_NUMBER              VARCHAR2(30 CHAR),
  LACT_CODE                VARCHAR2(100 CHAR),
  UPDATED_ON               DATE,
  UPDATED_BY               VARCHAR2(30 CHAR),
  REFERENCE                CLOB                DEFAULT EMPTY_CLOB(),
  OTHERS_INFO              CLOB                DEFAULT EMPTY_CLOB(),
  LACT_NUMBER_PREF         VARCHAR2(10 CHAR),
  LACT_NUMBER_SUFF         VARCHAR2(10 CHAR),
  INTERNAL_YN              VARCHAR2(1 CHAR)    DEFAULT 'N' NOT NULL,
  CONSTRAINT MJ_LACT_ACCEPTANCE_DATE_R_CHK CHECK (ACCEPTANCE_DATE_RULE_YN IN ('Y', 'N')),
  CONSTRAINT MJ_LACT_ACTUALITY_CHK CHECK (ACTUALITY_YN IN ('Y', 'N', 'Y/N', 'N/Y')),
  CONSTRAINT MJ_LACT_CONDITIONAL_PUBLIC_CHK CHECK (CONDITIONAL_PUBLIC_YN IN ('Y', 'N')),
  CONSTRAINT MJ_LACT_EDITION_NUM_CHK CHECK (EDITION_NUMBER >= -1),
  CONSTRAINT MJ_LACT_EFFECTIVE_DATE_R_CHK CHECK (EFFECTIVE_DATE_RULE_YN IN ('Y', 'N')),
  CONSTRAINT MJ_LACT_ELEMENT_EDITION_R_CHK CHECK (ELEMENT_EDITION_RULE_YN IN ('Y', 'N')),
  CONSTRAINT MJ_LACT_ELEMENT_TRANSITS_R_CHK CHECK (ELEMENT_TRANSITS_RULE_YN IN ('Y', 'N')),
  CONSTRAINT MJ_LACT_ID_CHK CHECK (LACT_ID > 0) DISABLE,
  CONSTRAINT MJ_LACT_MINOR_CHK CHECK (MINOR_YN IN ('Y', 'N')),
  CONSTRAINT MJ_LACT_PUBLIC_CHK CHECK (PUBLISHED_YN IN ('N', 'Y')),
  CONSTRAINT MJ_LACT_PUBLISHED_CHK CHECK (PUBLISHED_YN IN ('N', 'Y')),
  CONSTRAINT MJ_LACT_QUANTITY_PAGES_CHK CHECK (QUANTITY_PAGES IS NULL OR QUANTITY_PAGES > 0),
  CONSTRAINT MJ_LACT_ST_REG_NUM_CHK CHECK (ST_REG_NUMBER IS NULL OR LENGTH(ST_REG_NUMBER) = 8),
  CONSTRAINT MJ_LACT_TOC_CHK CHECK (TOC_YN IN ('N', 'Y')),
  CONSTRAINT MJ_LACT_PK PRIMARY KEY (LACT_ID) USING INDEX TABLESPACE MJ_INDX PCTFREE 2 STORAGE (INITIAL 80 K
                                                                                                MAXEXTENTS UNLIMITED),
  CONSTRAINT MJ_LACT_FORM_FK FOREIGN KEY (FORM_ID)
  REFERENCES CR.MJ_LACT_REL_SORTS_FORMS (REL_ID),
  CONSTRAINT MJ_LACT_INIT_FK FOREIGN KEY (INIT_LACT_ID)
  REFERENCES CR.MJ_LACTS (LACT_ID),
  CONSTRAINT MJ_LACT_LANG_FK FOREIGN KEY (LANG_ID)
  REFERENCES CR.CR_LOOKUP_VALUES (LOOKUP_VALUE_ID),
  CONSTRAINT MJ_LACT_OFFICIAL_SRC_FK FOREIGN KEY (OFFICIAL_SRC_ID)
  REFERENCES CR.CR_LOOKUP_VALUES (LOOKUP_VALUE_ID),
  CONSTRAINT MJ_LACT_STATE_FK FOREIGN KEY (STATE_ID)
  REFERENCES CR.CR_LOOKUP_VALUES (LOOKUP_VALUE_ID),
  CONSTRAINT MJ_LACT_STATUS_FK FOREIGN KEY (STATUS_ID)
  REFERENCES CR.CR_LOOKUP_VALUES (LOOKUP_VALUE_ID),
  CONSTRAINT MJ_LACT_VALIDITY_FK FOREIGN KEY (VALIDITY_ID)
  REFERENCES CR.CR_LOOKUP_VALUES (LOOKUP_VALUE_ID)
)
TABLESPACE MJ_DATA
INITRANS 2
STORAGE (INITIAL 80 K
         MAXEXTENTS UNLIMITED)
LOGGING
ENABLE ROW MOVEMENT;

CREATE INDEX CR.MJ_LACT_ACCEPT_DATE_IDX ON CR.MJ_LACTS (ACCEPTANCE_DATE)
TABLESPACE MJ_INDX
PCTFREE 2
STORAGE (INITIAL 64 K
         MAXEXTENTS UNLIMITED)
LOGGING;

CREATE BITMAP INDEX CR.MJ_LACT_ACTUALITY_IDX ON CR.MJ_LACTS (ACTUALITY_YN)
TABLESPACE MJ_INDX
PCTFREE 2
STORAGE (INITIAL 64 K
         MAXEXTENTS UNLIMITED)
LOGGING;

CREATE BITMAP INDEX CR.MJ_LACT_CONDITIONAL_PUBLIC_IDX ON CR.MJ_LACTS (CONDITIONAL_PUBLIC_YN)
TABLESPACE MJ_INDX
PCTFREE 2
STORAGE (INITIAL 64 K
         MAXEXTENTS UNLIMITED)
LOGGING;

CREATE INDEX CR.MJ_LACT_EDITION_NUM_IDX ON CR.MJ_LACTS (EDITION_NUMBER)
TABLESPACE MJ_INDX
PCTFREE 2
STORAGE (INITIAL 64 K
         MAXEXTENTS UNLIMITED)
LOGGING;

CREATE INDEX CR.MJ_LACT_EFFECTIVE_DATE_IDX ON CR.MJ_LACTS (EFFECTIVE_DATE)
TABLESPACE MJ_INDX
PCTFREE 2
STORAGE (INITIAL 64 K
         MAXEXTENTS UNLIMITED)
LOGGING;

CREATE BITMAP INDEX CR.MJ_LACT_ELEMENT_EDITION_R_IDX ON CR.MJ_LACTS (ELEMENT_EDITION_RULE_YN)
TABLESPACE MJ_INDX
PCTFREE 2
STORAGE (INITIAL 64 K
         MAXEXTENTS UNLIMITED)
LOGGING;

CREATE INDEX CR.MJ_LACT_FORM_IDX ON CR.MJ_LACTS (FORM_ID)
TABLESPACE MJ_INDX
PCTFREE 2
STORAGE (INITIAL 64 K
         MAXEXTENTS UNLIMITED)
LOGGING;

CREATE INDEX CR.MJ_LACT_INIT_IDX ON CR.MJ_LACTS (INIT_LACT_ID)
TABLESPACE MJ_INDX
PCTFREE 2
STORAGE (INITIAL 64 K
         MAXEXTENTS UNLIMITED)
LOGGING;

CREATE INDEX CR.MJ_LACT_KEY_FBI ON CR.MJ_LACTS (SUBSTR("LACT_CODE", INSTR("LACT_CODE", '_') + 1))
TABLESPACE MJ_INDX
PCTFREE 2
STORAGE (INITIAL 64 K
         MAXEXTENTS UNLIMITED)
LOGGING;

CREATE INDEX CR.MJ_LACT_LACT_NUMBER_IDX ON CR.MJ_LACTS (LACT_NUMBER)
TABLESPACE MJ_INDX
PCTFREE 2
STORAGE (INITIAL 64 K
         MAXEXTENTS UNLIMITED)
LOGGING;

CREATE INDEX CR.MJ_LACT_LANG_IDX ON CR.MJ_LACTS (LANG_ID)
TABLESPACE MJ_INDX
PCTFREE 2
STORAGE (INITIAL 64 K
         MAXEXTENTS UNLIMITED)
LOGGING;

CREATE BITMAP INDEX CR.MJ_LACT_MINOR_IDX ON CR.MJ_LACTS (MINOR_YN)
TABLESPACE MJ_INDX
PCTFREE 2
STORAGE (INITIAL 64 K
         MAXEXTENTS UNLIMITED)
LOGGING;

CREATE INDEX CR.MJ_LACT_OBS_DATE_IDX ON CR.MJ_LACTS (OBSOLETE_DATE)
TABLESPACE MJ_INDX
PCTFREE 2
STORAGE (INITIAL 64 K
         MAXEXTENTS UNLIMITED)
LOGGING;

CREATE INDEX CR.MJ_LACT_OFFICIAL_SRC_IDX ON CR.MJ_LACTS (OFFICIAL_SRC_ID)
TABLESPACE MJ_INDX
PCTFREE 2
STORAGE (INITIAL 64 K
         MAXEXTENTS UNLIMITED)
LOGGING;

CREATE INDEX CR.MJ_LACT_OWNER_IDX ON CR.MJ_LACTS (OWNER_ID)
TABLESPACE MJ_INDX
PCTFREE 2
STORAGE (INITIAL 64 K
         MAXEXTENTS UNLIMITED)
LOGGING;

CREATE BITMAP INDEX CR.MJ_LACT_PUBLIC_IDX ON CR.MJ_LACTS (PUBLIC_YN)
TABLESPACE MJ_INDX
PCTFREE 2
STORAGE (INITIAL 64 K
         MAXEXTENTS UNLIMITED)
LOGGING;

CREATE BITMAP INDEX CR.MJ_LACT_PUBLISHED_IDX ON CR.MJ_LACTS (PUBLISHED_YN)
TABLESPACE MJ_INDX
PCTFREE 2
STORAGE (INITIAL 64 K
         MAXEXTENTS UNLIMITED)
LOGGING;

CREATE INDEX CR.MJ_LACT_STATE_IDX ON CR.MJ_LACTS (STATE_ID)
TABLESPACE MJ_INDX
PCTFREE 2
STORAGE (INITIAL 64 K
         MAXEXTENTS UNLIMITED)
LOGGING;

CREATE INDEX CR.MJ_LACT_STATUS_IDX ON CR.MJ_LACTS (STATUS_ID)
TABLESPACE MJ_INDX
PCTFREE 2
STORAGE (INITIAL 64 K
         MAXEXTENTS UNLIMITED)
LOGGING;

CREATE INDEX CR.MJ_LACT_ST_REG_NUM_IDX ON CR.MJ_LACTS (ST_REG_NUMBER)
TABLESPACE MJ_INDX
PCTFREE 2
STORAGE (INITIAL 80 K
         MAXEXTENTS UNLIMITED)
LOGGING;

CREATE INDEX CR.MJ_LACT_TITLE_FBI ON CR.MJ_LACTS (UPPER("TITLE"))
TABLESPACE MJ_INDX
PCTFREE 5
STORAGE (INITIAL 64 K
         MAXEXTENTS UNLIMITED)
LOGGING;

CREATE INDEX CR.MJ_LACT_TOC_IDX ON CR.MJ_LACTS (TOC_YN)
TABLESPACE MJ_INDX
PCTFREE 2
STORAGE (INITIAL 64 K
         MAXEXTENTS UNLIMITED)
LOGGING;

CREATE UNIQUE INDEX CR.MJ_LACT_UK2 ON CR.MJ_LACTS (LACT_CODE)
TABLESPACE MJ_INDX
PCTFREE 2
STORAGE (INITIAL 64 K
         MAXEXTENTS UNLIMITED)
LOGGING;

CREATE INDEX CR.MJ_LACT_VALIDITY_IDX ON CR.MJ_LACTS (VALIDITY_ID)
TABLESPACE MJ_INDX
PCTFREE 2
STORAGE (INITIAL 64 K
         MAXEXTENTS UNLIMITED)
LOGGING;

CREATE INDEX CR.MJ_MJ_REG_DATE_IDX ON CR.MJ_LACTS (MJ_REG_DATE)
TABLESPACE MJ_INDX
PCTFREE 2
STORAGE (INITIAL 64 K
         MAXEXTENTS UNLIMITED)
LOGGING;

CREATE INDEX CR.MJ_MJ_REG_NUM_IDX ON CR.MJ_LACTS (MJ_REG_NUMBER)
TABLESPACE MJ_INDX
PCTFREE 2
STORAGE (INITIAL 64 K
         MAXEXTENTS UNLIMITED)
LOGGING;

Another interesting thing is that some time ago the test server also had something like an error. But it worked for a while. We haven't made any changes. We restarted the servers, but there is no change. How can I solve this problem?

The documentation on CATSEARCH describes its usage:

Use the CATSEARCH operator to search CTXCAT indexes.

create table mj_lacts
as
select
  level as id,
  lpad(level, 100, '0') as title,
  lpad(level, 4000, '0') as large_col
from dual
connect by level < 10

insert into mj_lacts
values(1, 'some text and law word', 'a')

SELECT *
FROM Mj_lacts
WHERE CATSEARCH(TITLE, 'law*', NULL) > 0

ORA-20000: Oracle Text error:
DRG-10599: column is not indexed

You need to create CTXCAT index and it works.

CREATE INDEX LACT_TITLE_TI ON MJ_LACTS(TITLE) 
       INDEXTYPE IS ctxsys.CTXcat;

SELECT *
FROM Mj_lacts
WHERE CATSEARCH(TITLE, 'law*', NULL) > 0

fiddle

Since you didn't provide all the information about both databases, it's hard to tell why it works in test and not in prod. But it's the same for all Oracle versions.

You may retrieve index DDL in test by dbms_metadata package:

select dbms_metadata.get_dependent_ddl(
  object_type => 'INDEX',
  base_object_name => 'MJ_LACTS'
  
)
from dual

fiddle