ORACLE 刪除無用分割槽

renjixinchina發表於2012-12-20
Oracle
1 刪除無資料用分割槽(按照使用者)
declare
  cnt int;
  p_name varchar2(50);
begin
  for aa in (select t.partition_name,t.table_name
               from dba_tab_partitions t
              where t.table_owner in ('NCCMOLTP') and t.partition_name not like '%410927%') loop
    begin
      --select count(*) into cnt from tjfrecord partition(aa.partition_name);
      execute immediate 'select count(*) from '||aa.table_name ||' partition('||aa.partition_name||')'
        into cnt;
      if cnt = 0 then
       
          execute immediate 'alter table '||aa.table_name||' drop partition '||aa.partition_name;
      
      end if;
    end;
  end loop;
end;
2 刪除無資料子分割槽(按照使用者)
declare
  cnt int;
  p_name varchar2(50);
begin
  for aa in (select t.subpartition_name,t.table_name
               from dba_tab_subpartitions t
              where t.table_owner in ('NCCMHIST') and t.subpartition_name not like '%410927%') loop
    begin
      --select count(*) into cnt from tjfrecord partition(aa.partition_name);
      execute immediate 'select count(*) from '||aa.table_name ||' subpartition('||aa.subpartition_name||')'
        into cnt;
      if cnt = 0 then

          execute immediate 'alter table '||aa.table_name||' drop subpartition '||aa.subpartition_name;

      end if;
    end;
  end loop;
end;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/15747463/viewspace-751402/，如需轉載，請註明出處，否則將追究法律責任。

相關文章