oracle按时间滚动分区,以半个小时为单位，滚动增加和删除表分区 一

以半个小时为单位，滚动增加和删除表分区[@more@]

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

要点：

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;

/