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 ltype(''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;
drop删除表
exit when cur%notfound;
v_sql:='alter table '||schema_name||'.'||table_name||' drop partition '||v_partition_name||' update global i
ndexes';
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')；

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。