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:
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
CATSEARCHoperator 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
| ID | TITLE | LARGE_COL |
|---|---|---|
| 1 | some text and law word | a |
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
| DBMS_METADATA.GET_DEPENDENT_DDL(OBJECT_TYPE=>'INDEX',BASE_OBJECT_NAME=>'MJ_LACTS') |
|---|
CREATE INDEX "FIDDLE_RMAAOVDFJEBDJXCLJUSU"."LACT_TITLE_TI" ON "FIDDLE_RMAAOVDFJEBDJXCLJUSU"."MJ_LACTS" ("TITLE") INDEXTYPE IS "CTXSYS"."CTXCAT" |