oracle定时任务记录

一、 Oracle定时任务说明
注意：所有的脚本都是在普通用户账号下登陆操作的(都是plsq的SQL窗口下执行的)。


1 创建分区表
create table vts_ais_history 
    (
       id                   VARCHAR2(64)     primary key    not null,
       lat                  NUMBER(10,4),
       lot                  NUMBER(10,4),
       mmsi                 VARCHAR2(32),
       create_date          DATE,
       in_db_date           DATE
    )PARTITION BY RANGE (create_date)  
    INTERVAL ( NUMTOYMINTERVAL (1, 'MONTH') )  
    (PARTITION part1 VALUES LESS THAN (TO_DATE ('2016-01-01', 'yyyy-MM-dd')))  


--创建全局索引：
create index create_date_index on vts_ais_history (create_date)


2 创建存储过程
create or replace procedure aish_proc_task
      as name varchar2(64);count_n int;
      begin
        count_n:=0;
        select count(*) into count_n
        from 
        (select * from user_objects where object_name='VTS_AIS_HISTORY' 
        and CREATED <= add_months(sysdate,-6)
        and GENERATED ='Y' order by created asc)
        where rownum=1;
        
        --dbms_output.put_line(count_n);
        if count_n>0 then
          --dbms_output.put_line('不为空1');
          select subobject_name into name 
          from 
          (select * from user_objects where object_name='VTS_AIS_HISTORY' 
            and CREATED <= add_months(sysdate,-6)
            and GENERATED ='Y' order by created asc)
            where rownum=1;
          if(name is not null) then 
                      --dbms_output.put_line('结果：');
                      --dbms_output.put_line(name);
                  EXECUTE IMMEDIATE 'alter table vts_ais_history drop partition '||name||' UPDATE GLOBAL INDEXES';  
            end if;
          
        --else 
          --dbms_output.put_line('为空');
        end if;
      end;


3 创建一个job作业，点击提交
declare  
          tm_job number;  
        begin  
          sys.dbms_job.submit(tm_job, --任务名称  ,--sys为管理员账号名
                'aish_proc_task;',--执行的过程  
                sysdate,--执行时间  
                'TRUNC(LAST_DAY(SYSDATE))+1+1/24');--每月1号的凌晨1点执行
          end;


4 得到job的id
select * from user_jobs


执行该sql语句，得到job的id值。就是job值。


5 运行job
begin  
            dbms_job.run(25);--25为任务的job的id值。user_jobs的job字段。  
          end;


6 删除job，点击提交
begin
           dbms_job.remove(25);--和select * from user_jobs; 中的job值对应，看what对应的存储过程名称
        end;




！！！说明：
1 存储过程中默认不容许执行alter table类似的sql语句。如果想要执行，通过execute   immediate 'truncate table tablename';
如：
创建存储过程：
create or replace procedure aish_proc_task
      as name varchar2(64);count_n int;
      begin
        count_n:=0;
        select count(*) into count_n
        from 
        (select * from user_objects where object_name='VTS_AIS_HISTORY' 
        and CREATED <= add_months(sysdate,-6)
        and GENERATED ='Y' order by created asc)
        where rownum=1;
        
        --dbms_output.put_line(count_n);
        if count_n>0 then
          --dbms_output.put_line('不为空1');
          select subobject_name into name 
          from 
          (select * from user_objects where object_name='VTS_AIS_HISTORY' 
            and CREATED <= add_months(sysdate,-6)
            and GENERATED ='Y' order by created asc)
            where rownum=1;
          if(name is not null) then 
                      --dbms_output.put_line('结果：');
                      --dbms_output.put_line(name);
                  EXECUTE IMMEDIATE 'alter table vts_ais_history drop partition '||name||' UPDATE GLOBAL INDEXES';  
            end if;
          
        --else 
          --dbms_output.put_line('为空');
        end if;
      end;
    
    执行存储过程：
    begin
    aish_proc_task;
    end;


2 存储过程中的select 。。结果集。如果结果集为空，则可以通过select count(*) into来进行判断。
    如上述的存储过程。


3 执行alter table vts_ais_history drop partition ‘分区名称’;sql语句后。再次插入时，会报索引不可用状态。
  解决方法：
      将：alter table vts_ais_history drop partition ‘分区名称’；
      改为：
      alter table vts_ais_history drop partition ‘分区名称’ UPDATE GLOBAL INDEXES;


4 执行频率改为每月1日凌晨1点执行 ，
      TRUNC(LAST_DAY(SYSDATE))+1+1/24
      如：
      declare  
              tm_job number;  
            begin  
              sys.dbms_job.submit(tm_job, --任务名称  ,--sys为管理员账号名
                    'aish_proc_task;',--执行的过程  
                    sysdate,--执行时间  
                    'TRUNC(LAST_DAY(SYSDATE))+1+1/24');--每月1日凌晨1点执行
              end;
              
       每1分钟执行一次示例：
        declare  
          tm_job number;  
        begin  
          sys.dbms_job.submit(tm_job, --任务名称  ,--sys为管理员账号名
                'aish_proc_task;',--执行的过程  
                sysdate,--执行时间  
                'SYSDATE+60/86400');
        end;