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;
Oracle job
最新推荐文章于 2023-12-16 10:45:00 发布