–用sys用户登录,解锁ctxsys用户
alter user ctxsys account unlock;–授权给对应用户
grant execute on ctx_ddl to yw;
–用yw用户登录
–创建分词,分词名为my_lexer
exec ctx_ddl.create_preference(‘my_lexer’,’chinese_lexer’);
–创建索引
create index IDX_ADDR_View on m_addr_view2(ADDR) indextype is CTXSYS.CONTEXT parameters(‘lexer my_lexer’);
–重建索引
ALTER INDEX IDX_ADDR_View REBUILD PARAMETERS(‘replace lexer my_lexer’);
–同步索引
begin
ctx_ddl.sync_index(‘IDX_ADDR_View’ );
end;
–优化索引
begin
ctx_ddl.optimize_index (‘IDX_ADDR_View’ ,’full’);
end;
–创建同步存储过程,同步表字段中的信息
create or replace procedure PROC_SYNC_ADDR_View as
begin
ctx_ddl.sync_index(‘IDX_ADDR_View’ );
end;
–创建任务,执行同步索引
VARIABLE jobno number;
BEGIN
DBMS_JOB.SUBMIT(:jobno,’PROC_SYNC_ADDR_View();’, SYSDATE, ‘SYSDATE + 7’);
commit;
END;
–创建优化存储过程
create or replace procedure PROC_OPTI_ADDR_View as
begin
ctx_ddl.optimize_index (‘IDX_ADDR_View’ ,’full’);
end;
–创建任务,执行同步索引
VARIABLE jobno number;
BEGIN
DBMS_JOB.SUBMIT(:jobno,’PROC_OPTI_ADDR_View();’, SYSDATE, ‘SYSDATE + 7’);
commit;
END;
–查看数据库中的所有 jobselect job,what,failures,broken from user_jobs –查看正在运行的job
select * from dba_jobs_running –删除jobBEGIN
DBMS_JOB.broken(4,true); –true表示停止 false表示暂停
DBMS_JOB.remove(4);
commit;
END;
