定时删除虚拟列分区

这是我的测试过程：
其中的表、存储过程、job等都需要根据生产环境进行修改

drop table vtest purge;

create table vtest (
day1 date,
day2 char(8),
fqkey date generated always as (to_date(day2,'YYYYMMDD'))) 
partition by range(fqkey)
interval (numtodsinterval(1,'day'))
(partition p20120115 values less than (to_date('20120115','YYYYMMDD')));


insert into vtest(day1,day2) values(sysdate-31,'20140101');
insert into vtest(day1,day2) values(sysdate-30,'20140102');
insert into vtest(day1,day2) values(sysdate-29,'20140103');
insert into vtest(day1,day2) values(sysdate-28,'20140104');
insert into vtest(day1,day2) values(sysdate-27,'20140105');
insert into vtest(day1,day2) values(sysdate-26,'20140106');
insert into vtest(day1,day2) values(sysdate-25,'20140107');
insert into vtest(day1,day2) values(sysdate-24,'20140108');
insert into vtest(day1,day2) values(sysdate-23,'20150109');
insert into vtest(day1,day2) values(sysdate-22,'20150110');
insert into vtest(day1,day2) values(sysdate-21,'20150121');
insert into vtest(day1,day2) values(sysdate-19,'20150111');
insert into vtest(day1,day2) values(sysdate-18,'20160131');
insert into vtest(day1,day2) values(sysdate-17,'20160112');
insert into vtest(day1,day2) values(sysdate-16,'20160113');
insert into vtest(day1,day2) values(sysdate-15,'20160114');
insert into vtest(day1,day2) values(sysdate-14,'20160115');
commit;


select segment_name,owner,segment_type,partition_name from dba_segments where segment_name='VTEST';


select * from vtest;


create or replace procedure lcltest is
 v_sql varchar2(400);
 v_table_name user_tab_partitions.table_name%type;
 v_partition_name user_tab_partitions.partition_name%type;
 v_high_value varchar(200);
 v_tmp_partition_name user_tab_partitions.partition_name%type;
 cursor cur is
   select
      table_name ,
      partition_name ,
      high_value 
    from user_tab_partitions 
    where partition_name like 'SYS%' ;
begin
  open cur;
  loop
    fetch cur into v_table_name,v_partition_name,v_high_value;
    exit when cur%notfound;
    v_tmp_partition_name := substr(v_high_value,11,10);
    v_tmp_partition_name := to_char( to_date(v_tmp_partition_name,'yyyy-mm-dd')-1 , 'yyyymmdd');
    dbms_output.put_line( v_tmp_partition_name  );
    if to_char(sysdate,'yyyymmdd')-v_tmp_partition_name > 7 then 
    --dbms_output.put_line( 'AAAAAA' );
    v_sql := 'alter table '||v_table_name||' drop partition '
      ||v_partition_name;
    dbms_output.put_line( v_sql );
    execute immediate v_sql;
    end if;
  end loop;
  close cur;
end lcltest;


begin
  DBMS_SCHEDULER.CREATE_JOB (
     job_name           =>  'proc_drop_vtest_partition',
     job_type           =>  'PLSQL_BLOCK',
     job_action         =>  'BEGIN call lcl.lcltest; END;',
     repeat_interval    =>  'FREQ=MINUTELY;INTERVAL=1',
     auto_drop          => false,
     enabled            => true,
   comments           =>  'My new job : to drop vtest partitions which are created a week ago');
end;
/


再次插入，进行测试