-- 删除索引的存储过程
create or replace procedure deleteIndex is
begin
execute immediate 'drop index gsc_test.GSCAPPROVEPROJECTCODEID';
execute immediate 'drop index gsc_test.GSCCODEID';
execute immediate 'drop index gsc_test.GSCORGANCODEID1';
end;
-- 创建索引的存储过程
create or replace procedure createIndex is
begin
execute immediate 'create index gsc_test.GSCCODEID on gsc_test.GSC_ORG_APP(GSCCODEID)' ;
execute immediate 'create index gsc_test.GSCAPPROVEPROJECTCODEID on gsc_test.GSC_ORG_APP(GSCAPPROVEPROJECTCODEID)';
execute immediate 'create index gsc_test.GscOrganCodeId1 on gsc_test.GSC_ORG_APP(gscOrganCodeId)';
end;
-- 定时任务
declare
job number;
BEGIN
DBMS_JOB.SUBMIT(
JOB => job, /*自动生成JOB_ID*/
WHAT => 'deleteIndex;', /*需要执行的存储过程名称或SQL语句*/
NEXT_DATE => to_date('20-07-2018 18:00:00','dd/mm/yyyy hh24:mi:ss'), /*初次执行时间-20170720下午6点*/
INTERVAL => 'trunc(sysdate)+1+18/24' /*每隔2分钟执行一次*/
);
commit;
end;