티스토리 뷰

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;
/

댓글
댓글쓰기 폼