利用排程任務定時刪除分割槽

生產環境需要定期刪除分割槽歷史資料，自己寫了一個，拿出來分享下吧。

1.建立排程任務字典表
-- Create table
create table SCHEDULER_CONF_DIC
(
  OWNER_NAME VARCHAR2(40),
  TABLE_NAME VARCHAR2(40),
  TYPE       VARCHAR2(30),
  REMAIN_DAY NUMBER
);
-- Add comments to the table 
comment on table SCHEDULER_CONF_DIC
  is '排程任務配置字典表';
-- Add comments to the columns 
comment on column SCHEDULER_CONF_DIC.OWNER_NAME
  is '表的擁有者';
comment on column SCHEDULER_CONF_DIC.TABLE_NAME
  is '表名稱';
comment on column SCHEDULER_CONF_DIC.TYPE
  is '型別';
comment on column SCHEDULER_CONF_DIC.REMAIN_DAY
  is '資料保留日期';


2.建立LONG_TO_VARCHAR的fuction


CREATE OR REPLACE FUNCTION LONG_TO_VARCHAR(P_TABLE_OWNER    IN ALL_TAB_PARTITIONS.TABLE_OWNER%TYPE,
                                           P_TABLE_NAME     IN ALL_TAB_PARTITIONS.TABLE_NAME%TYPE,
                                           P_PARTITION_NAME IN ALL_TAB_PARTITIONS.PARTITION_NAME%TYPE)
  RETURN VARCHAR2 AS
  L_HIGH_VALUE LONG;
BEGIN
  SELECT HIGH_VALUE
    INTO L_HIGH_VALUE
    FROM ALL_TAB_PARTITIONS
   WHERE TABLE_OWNER = P_TABLE_OWNER
     AND TABLE_NAME = P_TABLE_NAME
     AND PARTITION_NAME = P_PARTITION_NAME;


  RETURN SUBSTR(L_HIGH_VALUE, 1, 4000);
END;




3.建立測試表
create table test_drop
(
  logid      NUMBER not null,
 
  create_date DATE
)
PARTITION BY RANGE(create_date)  --按照時間進行的範圍分割槽 
INTERVAL(NUMTOYMINTERVAL(1，'MONTH')) --11g新特性 間隔分割槽
(
  PARTITION P1 VALUES LESS THAN(TO_DATE('2014-05-01','YYYY-MM-DD'))
);
insert into test_drop values (1,to_date('2015-03-04 12:12:12','yyyy-mm-dd hh24:mi:ss'));


commit;


4.配置排程任務字典表
    OWNER_NAME TABLE_NAME TYPE REMAIN_MON
1 SYS TEST_DROP DROP_PARTITION 1


5.建立儲存過程
CREATE OR REPLACE PROCEDURE SCHEDULER_DROP_PARTITION IS


  /*author:夜無傷
  do：定期刪除分割槽表資料
  
    */


  V_SQL     VARCHAR2(300);
  V_TRUNC   VARCHAR2(300);
  V_DROP    VARCHAR2(300);
  V_FIRST_P VARCHAR2(100);
BEGIN


  FOR V_SQL IN (SELECT TABLE_OWNER, TABLE_NAME, PARTITION_NAME, HIGH_VALUE
                  FROM (SELECT T.TABLE_OWNER,
                               T.TABLE_NAME,
                               T.PARTITION_NAME,
                               SUBSTR(LONG_TO_VARCHAR(T.TABLE_OWNER,
                                                      T.TABLE_NAME,
                                                      T.PARTITION_NAME),
                                      11,
                                      10) HIGH_VALUE
                          FROM DBA_TAB_PARTITIONS T,
                               (SELECT OWNER_NAME, TABLE_NAME
                                  FROM SCHEDULER_CONF_DIC
                                 WHERE TYPE = 'DROP_PARTITION'
                                   AND REMAIN_MON = 1) TT
                         WHERE T.TABLE_NAME = TT.TABLE_NAME
                           AND T.TABLE_OWNER = TT.OWNER_NAME)
                 WHERE HIGH_VALUE <
                       TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'yyyy-mm-dd')) LOOP
    DBMS_OUTPUT.PUT_LINE(V_SQL.TABLE_NAME || V_SQL.PARTITION_NAME);
    V_TRUNC := 'alter table ' || V_SQL.TABLE_OWNER || '.' ||
               V_SQL.TABLE_NAME || ' truncate partition ' ||
               V_SQL.PARTITION_NAME || '';
    V_DROP  := 'alter table ' || V_SQL.TABLE_OWNER || '.' ||
               V_SQL.TABLE_NAME || ' drop partition ' ||
               V_SQL.PARTITION_NAME || '';
    --DBMS_OUTPUT.PUT_LINE(V_TRUNC);
    --DBMS_OUTPUT.PUT_LINE(V_DROP);
    --EXECUTE IMMEDIATE V_TRUNC;
    --execute immediate v_drop;
  
    SELECT PARTITION_NAME
      INTO V_FIRST_P
      FROM (SELECT PARTITION_NAME
              FROM DBA_TAB_PARTITIONS T,
                   (SELECT OWNER_NAME, TABLE_NAME
                      FROM SCHEDULER_CONF_DIC
                     WHERE TYPE = 'DROP_PARTITION'
                       AND REMAIN_MON = 1) TT
             WHERE T.TABLE_NAME = TT.TABLE_NAME
               AND T.TABLE_OWNER = TT.OWNER_NAME
               AND T.PARTITION_POSITION = 1);
  
    IF V_SQL.PARTITION_NAME = V_FIRST_P THEN
      NULL;
      DBMS_OUTPUT.PUT_LINE(V_FIRST_P);
      DBMS_OUTPUT.PUT_LINE(V_TRUNC);
      EXECUTE IMMEDIATE V_TRUNC;
    
    ELSE
      DBMS_OUTPUT.PUT_LINE(V_TRUNC);
      DBMS_OUTPUT.PUT_LINE(V_DROP);
      EXECUTE IMMEDIATE V_TRUNC;
      EXECUTE IMMEDIATE V_DROP;
    END IF;
  END LOOP;
END;


   


6.建立排程任務


begin
 dbms_scheduler.create_job (
     job_name => 'SCHEDULER_DROP',
     job_type => 'STORED_PROCEDURE',
     job_action => 'SCHEDULER_DROP_PARTITION', --儲存過程名
     start_date => sysdate,
     repeat_interval => 'FREQ=MONTHLY; INTERVAL=1; BYMONTHDAY=1;BYHOUR=1;BYMINUTE=0;BYSECOND=0',  -- 按月，間隔為1個(月),每月1號,凌晨1點
     comments => '定期刪除分割槽表資料'
 );
end;


--建立後如果是它生效，需要啟用
-- job 啟用
begin
    dbms_scheduler.enable('SCHEDULER_DROP');
end;


7.查詢job資訊


-- job 查詢
SELECT OWNER,
       JOB_NAME,
       STATE,
       START_DATE,
       REPEAT_INTERVAL,
       LAST_START_DATE,
       NEXT_RUN_DATE
  FROM DBA_SCHEDULER_JOBS
 WHERE JOB_NAME = 'SCHEDULER_DROP'




    OWNER JOB_NAME STATE START_DATE REPEAT_INTERVAL LAST_START_DATE NEXT_RUN_DATE
1 SYS SCHEDULER_DROP SCHEDULED    23-4月 -15 04.07.25.000000 下午 +08:00 FREQ=MONTHLY; INTERVAL=1; BYMONTHDAY=1;BYHOUR=1;BYMINUTE=0;BYSECOND=0 01-5月 -15 01.00.00.500000 上午 +08:00




-- 手動執行job
begin
    dbms_scheduler.run_job('SCHEDULER_DROP',TRUE); -- true代表同步執行
end;


8.要停止job最好drop
-- 停止（不好用）
begin
    dbms_scheduler.stop_job(job_name => 'SCHEDULER_DROP',force => TRUE);
end;




-- job 刪除(對停job來說好用)
begin
    dbms_scheduler.drop_job(job_name => 'SCHEDULER_DROP',force => TRUE);)
end;