Oracle：管理 date类型 interval 动态变化的分区：查询、删除

-- 创建临时表，目的是获取long类型的：high_value 的值
drop table syscom_tab_partition_temp purge
;

create table prm8_user.syscom_tab_partition_temp (table_name varchar2(30),partition_position int,partition_name varchar(30),high_value clob, dt date )
;

-- 清理之前的临时数据
delete from syscom_tab_partition_temp a where a.table_name=upper('partition_table_name');
;

-- 获取partition表的分区信息
insert into syscom_tab_partition_temp(table_name,partition_position,partition_name,high_value)
select a.table_name,a.partition_position,a.partition_name,to_lob(a.high_value)
from user_tab_partitions  a
where a.table_name=upper('partition_table_name')
;

--特殊 date interval 类型表的 date值的处理
update syscom_tab_partition_temp
set dt=to_date(substr(trim(to_char(high_value)),11,19),'SYYYY-MM-DD HH24:MI:SS')
where table_name=upper('partition_table_name')
;

--获取删除脚本
select p.*
       ,'alter table ' || p.table_name || ' drop partition ' || p.partition_name || ';' as d_sql
from syscom_tab_partition_temp p
where p.table_name=upper('partition_table_name')
     and p.partition_name<>upper('PTN_DUMMY')
     and p.dt<trunc(sysdate - 180 ) -- 180 day
order by p.partition_position desc
;

--再次查询
select a.table_name,a.partition_position as pos,a.partition_name,a.interval,a.high_value,a.tablespace_name,a.num_rows,a.blocks,a.last_analyzed,a.empty_blocks,a.composite,a.subpartition_count,a.parent_table_partition
from user_tab_partitions  a
where a.table_name=upper('partition_table_name')
      and a.interval=upper('yes')
order by a.table_name,a.partition_position
;


--更新统计信息
begin
      dbms_stats.gather_table_stats(ownname => user,tabname => 'partition_table_name',cascade => true);
end;
/