Oracle文本索引

oracle全文检索需要ctxsys用户的支持，其实主要是需要使用ctxsys用户下的ctx_dl这个包，这个包中绝大部分过程的创建都与全文检索有关。

SELECT * FROM all_users WHERE username = 'CTXSYS'（查看是否存在ctxsys用户）

首先需要对ctxsys用户解锁，以获得ctx_ddl包的操作权。进入system用户，输入如下命令，

解锁ctxsys用户：

alter user CTXSYS account unlock ;

然后将ctx_ddl包的操作权限赋给testuser用户。

也是在system用户下，输入如下命令，赋予目标用户ctx_ddl包操作权限：

grant execute on ctx_ddl to MAXGDSG;

这里我使用chinese_lexer这个分词器，用testuser用户登录，执行下面的命令，创建分析器。

exec ctx_ddl.create_preference ( 'global_lexer ' , 'chinese_lexer' );

创建域索引

CREATE INDEX "MAXGDSG"."WORKORDER_NDX5" ON "MAXGDSG"."WORKORDER" ("JUSTIFYPRIORITY") 

   INDEXTYPE IS "CTXSYS"."CONTEXT" PARAMETERS ('lexer global_lexer language column LANGCODE');

能够执行下方SQL说明文本索引创建成功

SELECT * FROM workorder WHERE CONTAINS(JUSTIFYPRIORITY,'处理')>0

oracle提示文本索引报错，连续执行3次

call ctx_ddl.drop_preference('global_lexer');

call ctx_ddl.drop_preference('default_lexer');

call ctx_ddl.drop_preference('english_lexer');

call ctx_ddl.drop_preference('chinese_lexer');

call ctx_ddl.drop_preference('japanese_lexer');

call ctx_ddl.drop_preference('korean_lexer');

call ctx_ddl.drop_preference('german_lexer');

call ctx_ddl.drop_preference('dutch_lexer');

call ctx_ddl.drop_preference('swedish_lexer');

call ctx_ddl.drop_preference('french_lexer');

call ctx_ddl.drop_preference('italian_lexer');

call ctx_ddl.drop_preference('spanish_lexer');

call ctx_ddl.drop_preference('portu_lexer');

call ctx_ddl.create_preference('default_lexer','basic_lexer');

call ctx_ddl.create_preference('english_lexer','basic_lexer');

call ctx_ddl.create_preference('chinese_lexer','chinese_lexer');

call ctx_ddl.create_preference('japanese_lexer','japanese_lexer');

call ctx_ddl.create_preference('korean_lexer','korean_morph_lexer');

call ctx_ddl.create_preference('german_lexer','basic_lexer');

call ctx_ddl.create_preference('dutch_lexer','basic_lexer');

call ctx_ddl.create_preference('swedish_lexer','basic_lexer');

call ctx_ddl.create_preference('french_lexer','basic_lexer');

call ctx_ddl.create_preference('italian_lexer','basic_lexer');

call ctx_ddl.create_preference('spanish_lexer','basic_lexer');

call ctx_ddl.create_preference('portu_lexer','basic_lexer');

call ctx_ddl.create_preference('global_lexer', 'multi_lexer');

call ctx_ddl.add_sub_lexer('global_lexer','default','default_lexer');

call ctx_ddl.add_sub_lexer('global_lexer','english','english_lexer','en');

call ctx_ddl.add_sub_lexer('global_lexer','simplified chinese','chinese_lexer','zh');

call ctx_ddl.add_sub_lexer('global_lexer','japanese','japanese_lexer',null);

call ctx_ddl.add_sub_lexer('global_lexer','korean','korean_lexer',null);

call ctx_ddl.add_sub_lexer('global_lexer','german','german_lexer','de');

call ctx_ddl.add_sub_lexer('global_lexer','dutch','dutch_lexer',null);

call ctx_ddl.add_sub_lexer('global_lexer','swedish','swedish_lexer','sv');

call ctx_ddl.add_sub_lexer('global_lexer','french','french_lexer','fr');

call ctx_ddl.add_sub_lexer('global_lexer','italian','italian_lexer','it');

call ctx_ddl.add_sub_lexer('global_lexer','spanish','spanish_lexer','es');

call ctx_ddl.add_sub_lexer('global_lexer','portuguese','portu_lexer',null);