پاک کردن 10 پارتیشن آخر یک جدول

create table LIST_DROP_PART
( 
"tab_own" varchar2(128 byte), 
"tab_name" varchar2(128 byte), 
"interval" number
);

 

create table RESULT_DROP_PART
(    
"tab_own" varchar2(128 byte), 
"tab_name" varchar2(128 byte), 
"part_name" varchar2(128 byte), 
"high_value" long, 
"exec_date" date
);

 

create or replace procedure DROP_PARTITION_PRC as

  cursor tab_cur is
    select a.table_owner,
           a.table_name,
           a.partition_name,
           a.high_value,
           a.partition_position
      from dba_tab_partitions a
     where exists (select 1
              from list_drop_part b
             where a.table_owner = upper(b.tab_own)
               and a.table_name = upper(b.tab_name))
       and a.partition_position between 1 and
           (select max(c.partition_position) - 10
              from dba_tab_partitions c
             where a.table_owner = c.table_owner
               and a.table_name = c.table_name);

begin

  for tab_rec in tab_cur loop

    execute immediate 'alter table ' || tab_rec.table_owner || '.' ||
                      tab_rec.table_name || ' drop partition ' ||
                      tab_rec.partition_name || ' update global indexes ';
    insert into result_drop_part
    values
      (tab_rec.table_owner,
       tab_rec.table_name,
       tab_rec.partition_name,
       tab_rec.high_value,
       sysdate);
    commit;

    dbms_output.put_line('alter table ' || tab_rec.table_owner || '.' ||
                         tab_rec.table_name || ' drop partition ' ||
                         tab_rec.partition_name ||
                         ' update global indexes ');

  end loop;

end DROP_PARTITION_PRC;