Oracle 存储过程批量删除按日创建分区表的表分区

 

批量删除表分区思路：

　　1、通过查询 dba_tab_partitions 获取指定日期的表分区名字

　　2、alter table tableName drop partition partitionName update global indexes

　　注意：

　　1、此存储过程适用于按日创建的分区表

2、创建脚本前执行下面几个命令：
　　grant dba to etl;
　　grant alter any table to etl;
　　grant drop any table to etl;

create or replace procedure drop_partition(v_schema_name varchar2,v_table_name varchar2,v_start_date varchar2,v_end_date varchar2)
as
schema_name varchar2(50):=upper(v_schema_name);
table_name varchar2(50):=upper(v_table_name);
start_date varchar2(10):=v_start_date;
end_date varchar2(10):=v_end_date;

v_sql varchar(200);
v_partition_name varchar(50);

type refcur_type is ref cursor;
cur refcur_type;

begin
    execute immediate 'create table tmp_partition_name(partition varchar2(20),drop_date date)';
    execute immediate 'insert into tmp_partition_name
                       select partition,to_date(substr(high_value,instr(high_value,'''''''')+2,10),''yyyy-mm-dd'')-1 from (
                       with xml as (
                       select dbms_xmlgen.getxmltype(''select table_name, partition_name, high_value from dba_tab_partitions where table_owner='''''||schema_name||''''' and table_name='''''||table_name||''''''') as x
                       from dual
                       )
                       select extractValue(rws.object_value, ''/ROW/TABLE_NAME'') table_name,
                       extractValue(rws.object_value, ''/ROW/PARTITION_NAME'') partition,
                       extractValue(rws.object_value, ''/ROW/HIGH_VALUE'') high_value
                       from xml x,
                       table(xmlsequence(extract(x.x, ''/ROWSET/ROW''))) rws ORDER BY extractValue(rws.object_value, ''/ROW/TABLE_NAME''),extractValue(rws.object_value, ''/ROW/HIGH_VALUE'')
                       ) a
                       where to_date(substr(high_value,instr(high_value,'''''''')+2,10),''yyyy-mm-dd'')-1>=to_date('''||start_date||''',''yyyy-mm-dd'')
                       and to_date(substr(high_value,instr(high_value,'''''''')+2,10),''yyyy-mm-dd'')-1<to_date('''||end_date||''',''yyyy-mm-dd'')
                       and partition !=''PART_DFT''';
    open cur for 'select partition from tmp_partition_name order by drop_date asc';
    loop
      fetch cur into v_partition_name;
      exit when cur%notfound;
      v_sql:='alter table '||schema_name||'.'||table_name||' drop partition '||v_partition_name||' update global indexes';
      execute immediate v_sql;
      commit;
      end loop;
    close cur;
    execute immediate 'drop table tmp_partition_name purge';
    
end drop_partition;

　　执行：

　　删除 etl.table1 分区表一月份的数据 相当于删除分区字段 timeCol >='2020-01-01' and timeCol < date '2020-02-01' 的数据
　　SQL > exec drop_partition('ETL','TABLE1','2020-01-01','2020-02-01')；