以下sql语句中，以fx作为oracle的用户进行操作的，请复制代码的同学进行修改。
一、设置词法分析器
BEGIN
ctx_ddl.create_preference (‘fx_lexer’, ‘chinese_vgram_lexer’);
END;
/
二、针对数据表的特定字段建索引
CREATE INDEX I_DM_FILE ON DM_FILE (FILEBODY) indextype is ctxsys.context
parameters(‘lexer fx_lexer ‘);
三、创建存储过程同步更新与优化索引
prompt
prompt Creating procedure OPTIMIZE_I_DM_FILE
prompt =====================================
prompt
CREATE OR REPLACE PROCEDURE FX.optimize_i_dm_file
as
begin
execute immediate ‘begin ctx_ddl.optimize_index(”i_dm_file”,”FULL”);end;’;
END optimize_i_dm_file;
/
prompt
prompt Creating procedure SYNC_I_DM_FILE
prompt =================================
prompt
CREATE OR REPLACE PROCEDURE FX.sync_i_dm_file
as
begin
begin
execute immediate ‘begin ctx_ddl.sync_index(”i_dm_file”,”2M”);end;’;
end;
END sync_i_dm_file;
/
四、新建作业，定时执行上面创建的存储过程
begin
sys.dbms_job.submit(job => :job,
what => ‘SYNC_I_DM_FILE;’,
next_date => to_date(’22-04-2009 17:17:39′, ‘dd-mm-yyyy hh24:mi:ss’),
interval => ‘SYSDATE + (1/24/4)’);
commit;
end;
/
begin
sys.dbms_job.submit(job => :job,
what => ‘optimize_i_dm_file;’,
next_date => to_date(’22-04-2009 17:20:13′, ‘dd-mm-yyyy hh24:mi:ss’),
interval => ‘SYSDATE+1′);
commit;
end;
/
五、赋予作业运行的权限grant execute any procedure to fx;
六、测试
在pl/sql developer的Job上点右键–>run