通过分区技术定期删除大表中的过期（比如一年前）数据

说明：使用oracle JOB调用存储过程定期删除分区表SALE_DATA中过期的分区
SQL> select partition_name,tablespace_name,segment_type from user_segments where segment_name='SALE_DATA'  order by 2;


PARTITION_NAME       TABLESPACE_NAME      SEGMENT_TYPE
------------------------------ ------------------------------ ------------------
SALES_2016_1       TBS_SALES_2016_1      TABLE PARTITION
SALES_2016_10       TBS_SALES_2016_10      TABLE PARTITION
SALES_2016_11       TBS_SALES_2016_11      TABLE PARTITION
SALES_2016_12       TBS_SALES_2016_12      TABLE PARTITION
SALES_2016_2       TBS_SALES_2016_2      TABLE PARTITION
SALES_2016_3       TBS_SALES_2016_3      TABLE PARTITION
SALES_2016_4       TBS_SALES_2016_4      TABLE PARTITION
SALES_2016_5       TBS_SALES_2016_5      TABLE PARTITION
SALES_2016_6       TBS_SALES_2016_6      TABLE PARTITION
SALES_2016_7       TBS_SALES_2016_7      TABLE PARTITION
SALES_2016_8       TBS_SALES_2016_8      TABLE PARTITION
SALES_2016_9       TBS_SALES_2016_9      TABLE PARTITION
SALES_2017_1       TBS_SALES_2017_1      TABLE PARTITION




创建删除分区的存储过程
create or replace procedure drop_partition as 
  v_part_name varchar2(100);
  v_over_time number;
  v_err_num number;
  v_err_msg varchar2(100);
begin
  select min(partition_name) into v_part_name from user_tab_partitions where table_name='SALE_DATA';
  select months_between(sysdate,to_date(substr(min(partition_name),7,8),'yyyy-mm')) into v_over_time from user_tab_partitions where table_name='SALE_DATA';
  if v_over_time>2 then
execute immediate 'alter table sale_data drop partition '||v_part_name;
  end if;
EXCEPTION
  WHEN OTHERS THEN
v_err_num := SQLCODE;
v_err_msg := SUBSTR(SQLERRM,1,100);
dbms_output.put_line('执行错误: '||v_err_num||' 错误描述: '||v_err_msg);
end drop_partition;
/


创建JOB
declare 
  job number;
begin
  dbms_job.submit(job,'drop_partition;',sysdate,'sysdate+1');
end;
/


查看JOB号
SQL> select job from user_jobs;


       JOB
----------
23


手工执行进行测试
begin
dbms_job.run(23);
end;
/
成功删除第一个分区
SQL> select partition_name,tablespace_name,segment_type from user_segments where segment_name='SALE_DATA'  order by 2;


PARTITION_NAME       TABLESPACE_NAME      SEGMENT_TYPE
------------------------------ ------------------------------ ------------------
SALES_2016_10       TBS_SALES_2016_10      TABLE PARTITION
SALES_2016_11       TBS_SALES_2016_11      TABLE PARTITION
SALES_2016_12       TBS_SALES_2016_12      TABLE PARTITION
SALES_2016_2       TBS_SALES_2016_2      TABLE PARTITION
SALES_2016_3       TBS_SALES_2016_3      TABLE PARTITION
SALES_2016_4       TBS_SALES_2016_4      TABLE PARTITION
SALES_2016_5       TBS_SALES_2016_5      TABLE PARTITION
SALES_2016_6       TBS_SALES_2016_6      TABLE PARTITION
SALES_2016_7       TBS_SALES_2016_7      TABLE PARTITION
SALES_2016_8       TBS_SALES_2016_8      TABLE PARTITION
SALES_2016_9       TBS_SALES_2016_9      TABLE PARTITION
SALES_2017_1       TBS_SALES_2017_1      TABLE PARTITION

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

转载于:http://blog.itpub.net/30373263/viewspace-2064704/