/**
* @description call add partition procedure to add the latest partotion
* @description partition day is to_char(sysdate,'yyyymmdd')
*/
prc_s_maintain_partition(iv_char_date => to_char((trunc(sysdate,'DD')-to_char(sysdate,'D')+1),'yyyymmdd'),
on_statuscode => vv_par_flag);
/**
* @description exc_error2 will be raised if the return of sp_dw_tab_add_partition procedure is not 0
* @description get the vv_par_flag
*/
if vv_par_flag <>0 then
raise exc_error2;
end if;
/**
* @description clean all expired partitions, only the latest partition is keeping
* @description the latest partition's date is to_char(sysdate,'yyyymmdd')
*/
open vc_table_info;
loop
fetch vc_table_info
into l_table_info;
exit when vc_table_info%notfound;
if l_table_info.table_owner = vv_user1 or l_table_info.table_owner = vv_user2 or l_table_info.table_owner = vv_user3
or l_table_info.table_owner = vv_user4 then
if l_table_info.partition_name = l_table_info.partition_name2 then
vv_sql := '';
else
vv_sql := 'alter table ' ||l_table_info.table_owner||'.'|| l_table_info.table_name ||
' drop partition ' || l_table_info.partition_name;
execute immediate vv_sql;
end if;
end if;