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  awhere a.table_name=upper('partition_table_name');--特殊 date interval 類型表的 date值的處理update syscom_tab_partition_tempset 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_sqlfrom syscom_tab_partition_temp pwhere p.table_name=upper('partition_table_name')     and p.partition_name<>upper('PTN_DUMMY')     and p.dt<trunc(sysdate - 180 ) -- 180 dayorder 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_partitionfrom user_tab_partitions  awhere 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;/