MAX 파티션을 사용하면 인덱스도 리빌드해야하고, SPLIT해야 하고.. 골치아픕니다.
새로운 partition을 add만 하는 방식으로의 전환이 필요합니다.
일별로 아래 프로시져를 crontab에서 호출만 하면 아~주 간단하게 일별 파티션을 관리할 수 있습니다.
가끔 생성이 실패될 때가 있는데 이것까지 고려해서. 시간단위로 아래 프로시져를 돌리면 알아서 오늘로부터 10일이후까지,
오늘로부터 15일 이전까지 총 25일의 파티션을 항상 유지시켜줍니다.
CREATE OR REPLACE procedure proc_partitions
(
out_resultcode out varchar2, — 작업결과 코드.
out_sqlerrmsg out varchar2 — 오류시 oracle 에러코드/메시지.
)
is
/*************************************************************************************/
/* 파티셔닝된 테이블들을 불러와서 SPLIT SCRIPT를 만들고 이를 커서에 저장한다 */
/*************************************************************************************/
cursor c_part_tables
is
select * from (
select table_name,
decode(ordval, 1, ‘alter table ‘||table_name||’ add partition P’||npart||’ VALUES LESS THAN (”’||ndt||”’)’) addscript,
‘alter table ‘||table_name||’ drop partition P’||bdt||decode(table_name,’WORKLIST’,’ update global indexes’) dropscript, ndt, bdt, npart
from(
select table_name, len, decode(len,8,TO_CHAR(to_date(replace(max(partition_name),’P’,”),’yyyymmdd’)+1,’yyyymmdd’)) npart,
decode(len,8,TO_CHAR(to_date(replace(max(partition_name),’P’,”),’yyyymmdd’)+2,’yyyymmdd’)) ndt,
decode(len,8,TO_CHAR(to_date(replace(max(partition_name),’P’,”),’yyyymmdd’)-23,’yyyymmdd’)) bdt,
row_number() over (partition by table_name, len order by decode(len,8,TO_CHAR(to_date(replace(partition_name,’P’,”),’yyyymmdd’)-10,’yyyymmdd’)) desc) ordval
from (
select table_name, length(replace(partition_name,’P’,”)) len,
partition_name
from user_tab_partitions
where length(replace(partition_name,’P’,”)) is not null
and partition_name like ‘P2%’
)
where len = 8
group by table_name, len, decode(len,8,TO_CHAR(to_date(replace(partition_name,’P’,”),’yyyymmdd’)-10,’yyyymmdd’))
)
where ndt < to_char(sysdate+10,’yyyymmdd’)
) ;
v_sql varchar2(4000);
begin
for c_part in c_part_tables()
loop
begin
v_sql := c_part.addscript;
execute immediate v_sql;
exception
when others then
v_sql := ”;
end;
begin
v_sql := c_part.dropscript;
execute immediate v_sql;
exception
when others then
v_sql := ”;
end;
end loop;
exception
when others then
out_resultcode := -1;
out_sqlerrmsg := sqlerrm||’ (‘||sqlcode||’)’;
end proc_partitions;
/