티스토리 뷰

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; /
댓글
댓글쓰기 폼