BEGIN
ctx_ddl.create_preference ('my_lexer', 'chinese_vgram_lexer');
END;
/
其中my_lexer是分析器名。
二、建立全文索引
在建立intermedia索引时,指明所用的lexer:
CREATE INDEX myindex ON mytable(mycolumn) indextype is ctxsys.context parameters('lexer my_lexer');
※个人体会:全文索引建立后,用pl/sql developer工具view table,在index这一栏是看不到索引信息的。
而本人在删除全文索引时遇到过一下报错:
SQL> drop index searchkeytbl_key;
drop index searchkeytbl_key
ORA-29868: cannot issue DDL on a domain index marked as LOADING 解决方法:
ORA-29868: cannot issue DDL on a domain index marked as LOADING
说明:在创建索引的时候断开、重启等导致索引中断没有执行成功,之后再drop或者rebuild等操作的时候都会报此错误
解决:只能drop index ind_name force强行删除,然后再重建 三、索引同步维护
用以下的两个job来完成(该job要建在和表同一个用户下) :
※个人体会:运行job可能会有问题,此时可以单独运行索引,尝试一下
exec ctx_ddl.sync_index('index_name');
如果单独运行没有问题,则检查job是否写错或者当前操作的oracle数据库用户有无运行存储过程的权限
SQL> exec dbms_job.run(190);
begin dbms_job.run(190); end;
ORA-12011: execution of 1 jobs failed
ORA-06512: at "SYS.DBMS_IJOB", line 406
ORA-06512: at "SYS.DBMS_JOB", line 272
ORA-06512: at line 1
以上报错就是用户没有运行任何存储过程造成的,此时需要对用户加上这个权限:
SQL> grant execute any procedure to oracle_username;
再看一下job的情况
select * from user_jobs;
四、测试
关联查询: select * from table_name where contains (column_name,'keyword') >0;
SQL> select * from searchkeytbl where type='城市' and contains (key,'杨浦') >0;
USERNAME TYPE KEY
-------------------- ---------------------------------------- --------------------------------------------------------------------------------
mujian80 城市 上海市杨浦区
五、问题
加全文索引遇到的问题(不断更新)
SQL> create index gh_ghname_idx on gh(ghname) indextype is ctxsys.context parameters('lexer gh_ghname_lexer');
create index gh_ghname_idx on gh(ghname) indextype is ctxsys.context parameters('lexer gh_ghname_lexer')
ORA-24795: Illegal COMMIT attempt made
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-50857: oracle error in drvddl.IndexCreate
ORA-20000: Oracle Text error:
DRG-50857: oracle error in drvdml.MaintainKTab
ORA-24795: Illegal COMMIT attempt made
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 364
To avoid the error, please use one of the following solutions
1. Don't use a 32k-blocksized tablespace to store the internal index objects
- or -
2. Download Patch 5596325 from Metalink and apply it as described in the README file.
看一下 可能是用于创建索引的表空间不够了
reports——>DBA——>total free space pl/sql developer工具,查看表空间的剩余空间
select * from v$datafile; 查看数据文件信息