심슨 구름 - my life would suck without u

CREATE OR REPLACE procedure proc_partitions
(
  out_resultcode   out varchar2,                  -- 작업결과 코드.
  out_sqlerrmsg    out varchar2                   -- 오류시 oracle 에러코드/메시지.
)
is
 
  /*************************************************************************************/
  /* 파티셔닝된 테이블들을 불러와서 SPLIT SCRIPT를 만들고 이를 커서에 저장한다                                            */
  /*************************************************************************************/
  cursor c_part_tables
  is
  select table_name,  
        'alter table '||table_name||' split partition '        ||table_name||'_PMAX AT ('''||ndt||''')  
        INTO (partition '||table_name||'_P'||npart||', partition '||        table_name||'_PMAX)' addscript, 
        'alter table '||table_name||' drop partition '||        table_name||'_P'||bdt dropscript  
   from ( 
      select table_name,     decode(length(replace(max(partition_name),table_name||'_P','')),8,'DAY') len, 
             decode(length(replace(max(partition_name),table_name||'_P','')),8, 
             TO_CHAR(to_date(replace(max(partition_name),table_name||'_P',''),     'yyyymmdd')+1,'yyyymmdd')) npart, 
             decode(length(replace(max(partition_name),table_name||'_P','')),8, 
             TO_CHAR(to_date(replace(max(partition_name),table_name||'_P',''),     'yyyymmdd')+2,'yyyymmdd')) ndt,
             decode(length(replace(max(partition_name),table_name||'_P','')),8, 
             TO_CHAR(to_date(replace(max(partition_name),table_name||'_P',''),     'yyyymmdd')-7,'yyyymmdd')) bdt 
        from user_tab_partitions 
       where partition_name not like '%PMAX' 
       group by table_name); 
 
        cursor c_part_indexes
        is
        select 'alter index '||index_name||' rebuild partition '||partition_name indscript
          from user_ind_partitions
         where status = 'UNUSABLE'; 
 
        cursor c_nonpart_indexes
        is
        select 'alter index '||b.index_name||' rebuild' nindscript 
         from user_part_tables a, user_indexes b
        where a.table_name = b.table_name
          and b.PARTITIONED = 'NO'
          and b.status = 'UNUSUABLE';
 
        v_sql varchar2(4000);
        
begin
        for c_part in c_part_tables()
        loop
 
                v_sql := c_part.addscript;
                execute immediate v_sql;
                v_sql := c_part.dropscript;
                execute immediate v_sql;
                       
        end loop;
 
        for c_indx in c_part_indexes()
        loop
 
                v_sql := c_indx.indscript;
                execute immediate v_sql;
                                       
        end loop;
 
        for c_nindx in c_nonpart_indexes()
        loop
 
                v_sql := c_nindx.nindscript;
                execute immediate v_sql;
                                       
        end loop;
        
        
exception
     when others then
        out_resultcode := -1;
        out_sqlerrmsg := sqlerrm||' ('||sqlcode||')';
end proc_partitions;

-----------------------------------------------------------------------------
Niflheim님 블로그에서 프로시져를 퍼왔습니다.
-----------------------------------------------------------------------------

제가 관리하는 DB쪽에서 쓸수있게 프로시져를 약간 수정한뒤 Job schedule에 등록 시켰습니다.

DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
    (
      job        => X
     ,what       => 'DECLARE
  OUT_RESULTCODE VARCHAR2(200);
  OUT_SQLERRMSG VARCHAR2(200);

BEGIN
  OUT_RESULTCODE := NULL;
  OUT_SQLERRMSG := NULL;

  DB명.PARTITION_PROC ( OUT_RESULTCODE, OUT_SQLERRMSG );
  COMMIT;
END;'
     ,next_date  => to_date('13-02-2009 15:19:27','dd/mm/yyyy hh24:mi:ss')
     ,interval   => 'TRUNC(SYSDATE+7)'
     ,no_parse   => FALSE
    );
:JobNumber := to_char(X);
END;

매주마다 실행이 되도록 설정하였습니다.

밑에는 toad  job 추가화면 입니다.

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-동일조건변경허락 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.