这是我的测试过程:
其中的表、存储过程、job等都需要根据生产环境进行修改
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;
/
再次插入,进行测试
其中的表、存储过程、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;
/
再次插入,进行测试