使用 dbms_scheduler 定期删除大表中的历史记录

最新推荐文章于 2024-02-18 17:05:35 发布

转载最新推荐文章于 2024-02-18 17:05:35 发布 · 153 阅读

本文详细介绍了如何在Oracle RAC环境下使用PL/SQL编写一个存储过程WM71.DEL_HIS_BIZDOCCONTENT来删除历史文档内容，并通过调度器设置为每日定时执行此任务。

Oracle 10.2.0.4 , RAC

CREATE OR REPLACE PROCEDURE WM71.DEL_HIS_BIZDOCCONTENT(p_status OUT VARCHAR2)
AS
BEGIN

delete from wm71.bizdoccontent where
docid in
   ( select docid from wm53.bizdoc
          where doctimestamp < sysdate -40
               and doctimestamp >= sysdate -41) ;

commit ;

delete from wm71.bizdoccontent where
   docid in
     ( select docid from wm53.bizdoc
          where doctimestamp < sysdate -41
                 and doctimestamp >= sysdate -42) ;

commit ;

p_status := SQLCODE ;

   EXCEPTION
          WHEN OTHERS
   THEN
        p_status := SQLCODE || 'EXCEPTION OCCURED IN WM71.DEL_HIS_BIZDOCCONTENT PROCEDURE' ||

SUBSTR(SQLERRM,1,128);
-- DBMS_OUTPUT.PUT('EXCEPTION OCCURED IN WM71.DEL_HIS_BIZDOCCONTENT' || SQLCODE ||

SUBSTR(SQLERRM,1,400));
END;
/

begin
dbms_scheduler.create_job('DEL71_HIS_BIZDOCCONTENT_JOB',
   job_action=> 'WM71.DEL_HIS_BIZDOCCONTENT' ,
start_date => sysdate ,
   repeat_interval => 'FREQ=DAILY; BYHOUR=07,08 ; BYMINUTE=0,30',
   job_type=>'STORED_PROCEDURE', enabled=>TRUE);
end;
/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/35489/viewspace-712438/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/35489/viewspace-712438/