Wednesday, September 23, 2015

INTERVAL Partition Autp Deletion and addition

create table test_part
(
CREATED_DT            DATE
)
PARTITION BY RANGE (CREATED_DT)
INTERVAL(numtodsinterval(7,'day'))
(
PARTITION PART_01 VALUES LESS THAN('01-Sep-2015'),
PARTITION PART_02 VALUES LESS THAN('08-Sep-2015'),
PARTITION PART_03 VALUES LESS THAN('15-Sep-2015'),
PARTITION PART_04 VALUES LESS THAN('22-Sep-2015'),
PARTITION PART_05 VALUES LESS THAN('29-Sep-2015'),
PARTITION PART_06 VALUES LESS THAN('06-Oct-2015')
);
insert into test_part values('10-oct-2015');
declare
  i NUMBER(3):=1;
begin
  for x in (select partition_name
              from user_tab_partitions
              where table_name = 'TEST_PART' and partition_name not like 'PART_%')
  loop
       execute immediate ('alter table TEST_PART rename partition '||x.partition_name||' to PART_'||i);
        i:=i+1;              
  end loop;
end;
/

declare
  dt date;
begin
  for x in (select partition_name, high_value
              from user_tab_partitions
              where table_name = 'TEST_PART')
  loop
    execute immediate 'select '||x.high_value||' from dual' into dt;
    if dt < sysdate -20 then
      dbms_output.put_line('ALTER TABLE TEST_PART DROP PARTITION  '||x.partition_name);
      execute immediate ('ALTER TABLE TEST_PART DROP PARTITION  '||x.partition_name);
    end if;
  end loop;
end;
/
select partition_name, high_value from user_tab_partitions
where table_name = 'TEST_PART' and partition_name not like 'E__%';

No comments:

Post a Comment