删除分区

   --取删除明细表表的值
  select nvl(paramvalue,'90'), nvl(defaultvalue,'90'),paramrule
    into v_dstimedelanaly,v_defaultval,v_paramrule
    from st_systemparameter
   where upper(paramname) = upper('Sys_MaintailDetailTrace');
   select count(*)
     into v_exist
     from dual
    where regexp_like(v_dstimedelanaly,v_paramrule);
   if v_exist = 0 then
    v_dstimedelanaly := v_defaultval;
   end if;

  --删除过去明细表的分区
  select table_name, partition_name bulk collect
    into v_droptabname, v_droppart
    from user_tab_partitions
   where substr(table_name, 1, 6) = 'TBL_MM'
     and length(table_name) = 14
     and substr(partition_name, 2, 8) <
         to_char(sysdate - v_dstimedelanaly, 'yyyymmdd');

  if v_droptabname.count > 0 then
    for i in 1 .. v_droptabname.count
    loop
      --execute immediate 'drop table '||v_droptabname(i);
      v_sql := 'alter table ' || v_droptabname(i) || ' drop partition ' ||
               v_droppart(i) || ' update indexes';
      execute immediate v_sql;
    end loop;
  end if;
  commit;