Techdata VINA 지식 공유

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;

/