以周为单位，滚动增加和删除表分区， 保持一个13个周的时间窗。

要求： 数据保留3个月，大概就是13个周。过期的数据备份后删除。

这是对 快速删除大量数据 的继续。

要点：

1. 以'2000-01-02 00:00:00'为时间的起点，因为'2000-01-02 00:00:00'正好是周日。

2. 根据当前的sysdate，找出下一个周日的时间。

3. 从'2000-01-02 00:00:00'起到下一个周日，假设是n个周，n mod 13， 这样时间窗便可以滚动前进了。

4. 也不用为删数据发愁了。

5. 用脚本控制job正好在周日跑，傻瓜式的，不懂Oracle的人也能用。

1. 首先创建配置表：

drop table part_retention cascade constraints;

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

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

2. 创建13个表空间：

create tablespace edata0 datafile '/u02/oradata/transact/edata0.dbf' size 100M autoextend on next 10M maxsize unlimited nologging extent management local uniform size 1M segment space management auto;

create tablespace edata1 datafile '/u02/oradata/transact/edata1.dbf' size 100M autoextend on next 10M maxsize unlimited nologging extent management local uniform size 1M segment space management auto;

create tablespace edata2 datafile '/u02/oradata/transact/edata2.dbf' size 100M autoextend on next 10M maxsize unlimited nologging extent management local uniform size 1M segment space management auto;

create tablespace edata3 datafile '/u02/oradata/transact/edata3.dbf' size 100M autoextend on next 10M maxsize unlimited nologging extent management local uniform size 1M segment space management auto;


create tablespace edata4 datafile '/u02/oradata/transact/edata5.dbf' size 100M autoextend on next 10M maxsize unlimited nologging extent management local uniform size 1M segment space management auto;

create tablespace edata5 datafile '/u02/oradata/transact/edata5.dbf' size 100M autoextend on next 10M maxsize unlimited nologging extent management local uniform size 1M segment space management auto;


create tablespace edata6 datafile '/u02/oradata/transact/edata6.dbf' size 100M autoextend on next 10M maxsize unlimited nologging extent management local uniform size 1M segment space management auto;

create tablespace edata7 datafile '/u02/oradata/transact/edata7.dbf' size 100M autoextend on next 10M maxsize unlimited nologging extent management local uniform size 1M segment space management auto;


create tablespace edata7 datafile '/u02/oradata/transact/edata7.dbf' size 100M autoextend on next 10M maxsize unlimited nologging extent management local uniform size 1M segment space management auto;


create tablespace edata8 datafile '/u02/oradata/transact/edata8.dbf' size 100M autoextend on next 10M maxsize unlimited nologging extent management local uniform size 1M segment space management auto;

create tablespace edata8 datafile '/u02/oradata/transact/edata8.dbf' size 100M autoextend on next 10M maxsize unlimited nologging extent management local uniform size 1M segment space management auto;

create tablespace edata9 datafile '/u02/oradata/transact/edata9.dbf' size 100M autoextend on next 10M maxsize unlimited nologging extent management local uniform size 1M segment space management auto;

create tablespace edata9 datafile '/u02/oradata/transact/edata9.dbf' size 100M autoextend on next 10M maxsize unlimited nologging extent management local uniform size 1M segment space management auto;

create tablespace edata10 datafile '/u02/oradata/transact/edata10.dbf' size 100M autoextend on next 10M
maxsize unlimited nologging extent management local uniform size 1M segment space management auto;


create tablespace edata10 datafile '/u02/oradata/transact/edata10.dbf' size 100M autoextend on next 10M maxsize unlimited nologging extent management local uniform size 1M segment space management auto;


create tablespace edata11 datafile '/u02/oradata/transact/edata11.dbf' size 100M autoextend on next 10M
maxsize unlimited nologging extent management local uniform size 1M segment space management auto;

create tablespace edata11 datafile '/u02/oradata/transact/edata11.dbf' size 100M autoextend on next 10M maxsize unlimited nologging extent management local uniform size 1M segment space management auto;

create tablespace edata12 datafile '/u02/oradata/transact/edata12.dbf' size 100M autoextend on next 10M
maxsize unlimited nologging extent management local uniform size 1M segment space management auto;


create tablespace edata12 datafile '/u02/oradata/transact/edata12.dbf' size 100M autoextend on next 10M maxsize unlimited nologging extent management local uniform size 1M segment space management auto;

3. 创建滚动增加和删除分区的脚本：

/**************************************************************************

Program Name:Add_Partition

Description: Drop partition for 13th-week ago, create partion for next week

user:user

***************************************************************************/

CREATE OR REPLACE procedure add_partition
as

CURSOR c_td_table IS SELECT table_name, tablespace_name FROM part_retention;


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

v_week number;
v_last_sunday date;
v_next_sunday 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(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;

select trunc(decode(to_char((sysdate ),'D'), 1, sysdate,
2, sysdate - 1,
3, sysdate - 2,
4, sysdate - 3,
5, sysdate - 4,
6, sysdate - 5,
7, sysdate - 6
)) into v_last_sunday
from dual;

v_next_sunday := v_last_sunday + 7;

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_week;
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_week;
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_week || ' VALUES LESS THAN ( to_date ( ''' || to_char(v_next_sunday, 'yyyy-mm-dd hh24:mi:ss' ) || ''',''yyyy-mm-dd hh24:mi:ss'')) ' || 'tablespace ' || v_table.tablespace_name || v_week;

begin
dbms_output.put_line(sql_stmt);
execute immediate sql_stmt;
end;

END;
END LOOP;

END;
/
show errors;
commit;



exec add_partition ();

4. 创建一个添加在周日跑job的脚本，这样就很容易添加在周日跑的job了。

create or replace procedure add_sunday_job is

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

begin

select trunc(decode(to_char((sysdate ),'D'), 1, sysdate,
2, sysdate - 1,
3, sysdate - 2,
4, sysdate - 3,
5, sysdate - 4,
6, sysdate - 5,
7, sysdate - 6
)) + 7 into v_sunday
from dual;

v_what := 'begin add_partition; 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_sunday
,interval => 'SYSDATE + 7');
COMMIT;
end;
/
show errors;

exec add_sunday_job;