创建定时任务删除Oracle分区

要完成这个功能，需要创建一个存储过程和一个定时任务

--建测试分区表

CREATE TABLE "DB"."NXZ_TEST" 
   (	"name" VARCHAR2(400), 
	"dates" TIMESTAMP (6) DEFAULT sysdate, 
	 CONSTRAINT "NXZ_TEST_PK" PRIMARY KEY ("name")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "DB_DAT"  
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "DB_DAT" 
  PARTITION BY RANGE ("dates") INTERVAL (NUMTOYMINTERVAL(1, 'MONTH')) 
 (PARTITION "PART_T01"  VALUES LESS THAN (TIMESTAMP' 2018-11-01 00:00:00') SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING 
  STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "DB_DAT" ) ; 

--插入数据到测试表
INSERT INTO NXZ_TEST (name, dates) VALUES('aa1', to_date('2019-01-01','yyyy--MM-dd'));
INSERT INTO NXZ_TEST (name, dates) VALUES('aa2', to_date('2019-02-01','yyyy--MM-dd'));
INSERT INTO NXZ_TEST (name, dates) VALUES('aa3', to_date('2019-03-01','yyyy--MM-dd'));
INSERT INTO NXZ_TEST (name, dates) VALUES('aa4', to_date('2019-04-01','yyyy--MM-dd'));
INSERT INTO NXZ_TEST (name, dates) VALUES('aa5', to_date('2019-05-01','yyyy--MM-dd'));

--创建存储过程
CREATE OR REPLACE PROCEDURE DROP_PARTITION(i_table_name IN varchar2,
i_retain_partition IN NUMBER) IS
--by nxz
--参数1：表名
--参数2：保留的分区数 ，必须大于等于2(因为保留默认分区和最大分区 )
--oracle interval分区方式的第一个分区不能被删除,最大分区也不应该被删除，因此最少都会保持有默认分区和最大分区
--存储过程调用方式   CALL EDU_LDA.DROP_PARTITION('NXZ_TEST',2);
 TYPE type_array IS TABLE OF varchar(20) INDEX BY binary_integer;
v_partition_count NUMBER(4);
v_partition_name_array type_array;
v_SqlExec varchar(200);
BEGIN
    IF i_retain_partition >= 2 THEN
    SELECT count(1) INTO v_partition_count FROM USER_TAB_PARTITIONS WHERE table_name = i_table_name;
        SELECT partition_name BULK COLLECT INTO v_partition_name_array
            FROM USER_TAB_PARTITIONS WHERE table_name = i_table_name
                AND partition_position>1  AND partition_position<v_partition_count AND partition_position<=v_partition_count-(i_retain_partition-1) ;
    FOR i IN 1 .. v_partition_name_array.count
    LOOP
        v_SqlExec := 'ALTER TABLE ' || i_table_name ||'   DROP PARTITION ' || v_partition_name_array(i) || ' UPDATE GLOBAL indexes';
        DBMS_Utility.Exec_DDL_Statement(v_SqlExec);
    END
    LOOP;
    END IF;
END;

--定义定时Job drop_table_partition
declare
  drop_table_partition number;
BEGIN
  DBMS_JOB.SUBMIT( 
        JOB => drop_table_partition,  /*自动生成JOB_ID*/ 
        WHAT => 'DB.DROP_PARTITION(''TABLE1'',4);
DB.DROP_PARTITION(''TABLE2'',4);',  /*需要执行的存储过程名称或SQL语句*/ 
        NEXT_DATE => sysdate,  /*初次执行时间-立即执行*/ 
        INTERVAL => 'TRUNC(LAST_DAY(SYSDATE))+3+1/24' /*每月3日凌晨1点执行*/
      ); 
  commit;
end;

--查看当前用户定时任务
 
select * from user_jobs;
 
--手动执行定时任务
BEGIN
DBMS_JOB.RUN(101); --101为任务的ID
END;
 
 
--删除定时任务
begin
  dbms_job.remove(106);  /*删除自动执行的job,参数是 job的id*/
  commit;
END;
 
--查看表的所有分区
SELECT * FROM    USER_TAB_PARTITIONS WHERE table_name = 'NXZ_TEST' ;
 
--手动执行存储过程
CALL DROP_PARTITION('NXZ_TEST',1);