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__%';
(
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