为了试验以周为单位滚动增加和删除表分区，特地做了一个以半个小时为单位滚动前进的实验。

要点：

1. 一天24个小时恰好可以分成48个半个小时。故不需要一个特定的时间起点。

2。 注意round 和floor的区别。

1. 创建配置表：

drop table part_retention_t cascade constraints;
--delete part_retention;

create table part_retention_t (table_name varchar2(100), tablespace_name varchar(50), retention number);

Insert into part_RETENTION (TABLE_NAME, TABLESPACE_NAME, RETENTION) Values ('TEST', 'EDATA', 60);


commit;

2. 增加和删除用的脚本：

CREATE OR REPLACE procedure add_partition_t
as

CURSOR c_td_table IS SELECT table_name, tablespace_name FROM part_retention_t;


v_partition VARCHAR2 (30);
sql_stmt_err VARCHAR2 (4000);

v_half number;
v_which number;
v_this_half date;

v_pre_part number := 0;

v_turnaround number := 13;

sql_stmt VARCHAR2 (1000); -- String used to save sql statement
err_msg VARCHAR2 (300);


BEGIN

select mod(to_number(to_char(sysdate, 'HH')) * 2 + decode((floor(to_number(to_char(sysdate, 'MI')) / 30 )), 0,0,1,1), 13) into v_half from dual;

select decode((round(to_number(to_char(sysdate, 'MI')) / 30 , 0)), 0,0,1) into v_which from dual;

select trunc(sysdate, 'HH') + (1 / 24 / 2 ) * (3 + decode((floor(to_number(to_char(sysdate, 'MI')) / 30 )), 0,1,1,2,1)) into v_this_half from dual;


--select mod(round((trunc(sysdate ) - trunc(to_date('2000-01-02 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))) / 7, 0), v_turnaround) into v_week from dual;




FOR v_table IN c_td_table
LOOP

BEGIN

begin
select 1 into v_pre_part from user_tab_partitions where table_name = v_table.table_name AND partition_name = 'P' || v_half;
dbms_output.put_line('v_pre_part is:: ' || v_pre_part);
if (v_pre_part is not null) then
sql_stmt := 'alter table ' || v_table.table_name || ' drop partition ' || 'P' || v_half;
dbms_output.put_line('drop stmt is:: ' || sql_stmt);
execute immediate sql_stmt;

end if;
exception when others then
dbms_output.put_line(substr(SQLERRM, 1, 255));
null;
end;

sql_stmt := 'ALTER TABLE '|| v_table.table_name || ' ADD PARTITION ' || 'P' || v_half || ' VALUES LESS THAN ( to_date ( ''' || to_char(v_this_half, 'yyyy-mm-dd hh24:mi:ss' ) || ''',''yyyy-mm-dd hh24:mi:ss'')) ' || 'tablespace ' || v_table.tablespace_name || v_half;

begin
dbms_output.put_line(sql_stmt);
execute immediate sql_stmt;
exception when others then null;
end;

END;
END LOOP;

END;
/

3. 添加job用的脚本：

CREATE OR REPLACE procedure add_sunday_job_t is

v_sunday date;
v_job BINARY_INTEGER;
v_what VARCHAR2(200);
v_date date;
begin

select trunc(sysdate, 'HH') + (1 / 24 / 2 ) * decode((round(to_number(to_char(sysdate, 'MI')) / 30 , 0)), 0,1,2,2,1) into v_date from dual;

v_what := 'begin add_partition_t; end;';
begin
select job into v_job from user_jobs where what = v_what;
DBMS_JOB.REMOVE(v_job);
EXCEPTION
WHEN OTHERS THEN NULL;
end;

DBMS_JOB.SUBMIT (
job => v_job
,what => v_what
,next_date => v_date - 1 / 24 / 30
,interval => 'SYSDATE + 1/24/2');
COMMIT;
end;
/