Oracle job

declare 

   job1 number; 
begin
  sys.dbms_job.submit(job1,'PRO_MESSAGE_DELETE;',sysdate, 'TRUNC(LAST_DAY(SYSDATE))+1+1/24');
  commit;
end;
/


begin  
　　　　dbms_job.run(7);  
end;  
　　　/  


BEGIN
  DBMS_SCHEDULER.CREATE_JOB(job_name        => 'DELETE_SYSMESSAGE_JOB',       
                            job_type        => 'STORED_PROCEDURE',
                            job_action      => 'PRO_MESSAGE_DELETE',
                            repeat_interval => 'FREQ=MONTHLY; INTERVAL=1',
                            enabled         => true,
                            comments        => 'fetch data',
                            start_date      => TRUNC(LAST_DAY(SYSDATE))+4+1/24               
                            );
END;
/

select to_char(t.start_date,'yyyy-MM-dd HH:mm:ss ') from user_scheduler_jobs t where t.job_name='DELETE_SYSMESSAGE_JOB'


CREATE OR REPLACE PROCEDURE "PRO_MESSAGE_DELETE"
is
names     varchar2(1024);    --分区名称
dates     varchar2(1024);        --分区日期
temp    varchar2(1024);
v_temp   varchar2(1024);
v_sql   varchar2(1024);
v_cs    varchar2(1024);
v_error varchar2(1024);
TYPE t_ref_cursor IS REF CURSOR;
  c t_ref_cursor;
--获取分区列表
begin
v_cs := 'select PARTITION_NAME,HIGH_VALUE
  from USER_TAB_PARTITIONS
 where TABLE_NAME = upper(''us_internalinfo'')';
  open c for v_cs;
  fetch c
  into names,dates;
   while c% found loop
       --获得分区时间
       v_temp := 'select to_char('||dates||',''yyyy-MM-dd'') from dual';
       execute immediate v_temp into temp ;

        --时间比较
       if (months_between(trunc(sysdate,'mm'),trunc(to_date(temp,'yyyy-mm-dd'),'mm'))>=3)
       then
             ---删除分区
            v_sql := 'alter table us_internalinfo truncate partition ' ||names;
             execute immediate v_sql;
       end if;
   --取下一条数据
   fetch c  into names,dates;
   end loop;
   close c;
   commit;

   exception
   when others then
   v_error := sqlcode || ' - ' || sqlerrm;
   dbms_output.put_line(v_error);
 rollback;
end PRO_MESSAGE_DELETE;