create or replace procedure test
as
query_str varchar2(4000);
CURSOR PartSet IS SELECT PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='TRAININFO' and substrb(PARTITION_NAME,-8)<to_char(sysdate-30,'yyyymmdd');
begin
FOR thisPart IN PartSet LOOP
query_str :='ALTER TABLE TRAININFO DROP PARTITION '||thisPart.PARTITION_NAME;
execute immediate query_str;
insert into a values(sysdate);
commit;
END LOOP;
end;
/
这是删除30天前的所有分区
as
query_str varchar2(4000);
CURSOR PartSet IS SELECT PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='TRAININFO' and substrb(PARTITION_NAME,-8)<to_char(sysdate-30,'yyyymmdd');
begin
FOR thisPart IN PartSet LOOP
query_str :='ALTER TABLE TRAININFO DROP PARTITION '||thisPart.PARTITION_NAME;
execute immediate query_str;
insert into a values(sysdate);
commit;
END LOOP;
end;
/
这是删除30天前的所有分区
create or replace procedure test
as
query_str varchar2(4000);
CURSOR PartSet IS SELECT PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='TRAININFO' and substrb(PARTITION_NAME,-8)<to_char(sysdate-30,'yyyymmdd');
begin
FOR thisPart IN PartSet LOOP
query_str :='ALTER TABLE TRAININFO TRUNCATE PARTITION '||thisPart.PARTITION_NAME;
execute immediate query_str;
query_str :='ALTER TABLE TRAININFO DROP PARTITION '||thisPart.PARTITION_NAME;
execute immediate query_str;
insert into a values(sysdate);
commit;
END LOOP;
end;
/