orale创建任务过程(Job)

 

 第一步：创要定时执行的存储过程(plsql中执行即可)  

create or replace procedure p_rebuild_all_index
as
sqlt varchar(1000);
tablespace_name varchar(1000);
v_sucess varchar(1000);
begin
tablespace_name:='TYBOOKDB';
v_sucess:='重建成功';
for idx in (select index_name, tablespace_name, status,table_name from user_indexes where upper(tablespace_name)=upper(tablespace_name) and status='VALID' and temporary = 'N' and index_type='NORMAL') loop
begin
sqlt := 'alter index ' || idx.index_name || ' rebuild ';
dbms_output.put_line('表名:'||idx.table_name);
dbms_output.put_line('索引名称:'||idx.index_name);
dbms_output.put_line('执行语句:'||sqlt);
EXECUTE IMMEDIATE sqlt;
--错误后循环继续执行。
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
v_sucess:='重建失败';
end;
end loop;
--写日志，成功或失败
insert into t_tybook_back_log
( user_loginname, login_date, oper_type, oper_date, oper_des, oper_code, model_name)
values
( 'oracle_job', sysdate, '重建索引任务', sysdate, v_sucess, '', '');

end;

 

 

 第二步：通过PL/SQL创建JOB,约定每天早上2:00执行此存储过程

 

declare job number;
begin
sys.dbms_job.submit(job ,'p_rebuild_all_index;',to_date('18-10-2012 02:00:00', 'dd-mm-yyyy hh24:mi:ss') ,'sysdate+1');
end;

 

第三步:启动JOB任务

begin
exec dbms_job.run(:job1);
end;