--创建一个带分区的表，这里主要是List分区，其它分区也一样。
drop table part_test;
create table part_test (
empno number(4),
ename varchar2(30),
location varchar2(30)
)
partition by list (location)
(
partition p1 values ('北京'),
partition p2 values ('上海','天津','重庆'),
partition p3 values ('广东','福建'),
partition p0 values (default)
);




# 删除分区的 SQL 语法如下:
alter table part_test drop partition p0; 




## 创建清理配置表
create table a_part_tab_clean_lst(
table_owner varchar2(30),--模式名
table_name varchar2(30), --表名
retain_days number       --数据保留天数
);




## 初如化数据
insert into a_part_tab_clean_lst values('SCOTT','G_T_ORG_DPST_AGG_H',60);
insert into a_part_tab_clean_lst values('SCOTT','G_T_PSN_CST_DPST_AGG_H',90);
insert into a_part_tab_clean_lst values('SCOTT','G_T_DPST_AC_BAL_H',120);
commit;




## 创建清理日志表
create table a_part_tab_clean_log(
clean_timestamp timestamp, --清理的时间截
table_owner varchar2(30),  --模式名
table_name varchar2(30),   --表名
partition_name varchar2(30), --分区名
partition_value varchar2(360) --分区值
);








## 删除分区存储过程 proc_drop_part.
create or replace procedure proc_tab_drop_part (
v_table_owner varchar2,
v_table_name varchar2,
v_part_name varchar2
)
as
v_str varchar2(600);
begin
--生成删除分区的动态SQL语句.
v_str := 'alter table '||v_table_owner||'.'||v_table_name||' drop partition '||v_part_name;
--执行上一步生成的动态SQL.
execute immediate v_str;
insert into scott.a_part_tab_clean_log values(systimestamp,v_table_owner,v_table_name,v_part_name,substr(replace(v_part_name,'P_',''),1,4)||'-'||substr(replace(v_part_name,'P_',''),5,2)||'-'||substr(replace(v_part_name,'P_',''),7,2));
exception 
when others then 
null;
commit; 
end; 
/










## 单独清理表存储过程.
create or replace procedure proc_tab_clean_part(
v_table_owner varchar2,
v_table_name varchar2,
v_retain_days number
)
as
cursor cur1 is select trim(table_owner) table_owner,trim(table_name) table_name,trim(partition_name) partition_name from dba_tab_partitions where table_owner=v_table_owner and table_name=v_table_name and (sysdate - to_date(replace(partition_name,'P_',''),'yyyymmdd')) >= v_retain_days;
begin
for cur_rec in cur1 loop
proc_tab_drop_part(cur_rec.table_owner,cur_rec.table_name,cur_rec.partition_name);
end loop;
end;
/




## 批量清理表存储过程.
create or replace procedure proc_tab_clean_part_batch
as
cursor cur2 is select trim(table_owner) table_owner,trim(table_name) table_name,retain_days from scott.a_part_tab_clean_lst;
begin
for cur_rec2 in cur2 loop
proc_tab_clean_part(cur_rec2.table_owner,cur_rec2.table_name,cur_rec2.retain_days);
end loop;
end;
/