Oracle添加job定时执行存储过程

1 、创建存储过程 ( 删除临时表 ) ：

create or replace procedure sp_gs_clear_temp_tables is
tmp_tbl_name varchar(30);
sqltext varchar(255);
cursor cur_tmp_tables is select object_name from user_objects where object_name like 'TMP%' and length(object_name) > 13 and object_type = 'TABLE' and created < (sysdate -2);
begin
open cur_tmp_tables;
    fetch cur_tmp_tables into tmp_tbl_name;
    loop
          exit when not cur_tmp_tables%found;
          sqltext := 'drop table ' || tmp_tbl_name;
          execute immediate sqltext;
          fetch cur_tmp_tables into tmp_tbl_name;
    end loop;
close cur_tmp_tables;
end sp_gs_clear_temp_tables;

2、创建JOB

SQL> variable job1 number;

SQL> begin

  2  dbms_job.submit(:job1,'sp_gs_clear_temp_tables;', sysdate,'sysdate+1/1440');

  3  end;

  4  /

 

PL/SQL procedure successfully completed

 

3、运行JOB

SQL> begin

  2  dbms_job.run(:job1);

  3  end;

  4  /

 

PL/SQL procedure successfully completed

 

4、删除JOB

SQL> begin

  2  dbms_job.remove(:job1);

  3  end;

  4  /

 

PL/SQL procedure successfully completed

 

5、计划任务执行情况监控

　　select * from user_jobs;--查看调度任务

　　select * from dba_jobs_running;--查看正在执行的调度任务

select * from dba_jobs;--查看执行完的调度任务