create or replace procedure PROC_ADD_PARTITION(
  P_SEGMENT_NAME in varchar2 default null
) is
  type TYPE_CURSOR is ref cursor;
  GV_LOC_STOP varchar2(64);
  C_PT TYPE_CURSOR;
  C_SPT TYPE_CURSOR;
  
  V_SQL_PT varchar2(1024) := '';
  V_SQL_PTT varchar2(1024) := '';
  V_SQL_PT_SPLIT varchar2(1024) := '';
  
  V_SQL_SPT varchar2(1024) := '';
  V_SQL_SPT_SPLIT varchar2(1024) := '';
  V_SQL_SPT_TMP varchar2(1024) := '';
  
  V_SEGMENT_NAME varchar2(1024) := '';
  V_SEGMENT_TYPE varchar2(1024) := '';
  V_PARTITION_NAME varchar2(1024) := '';
  V_COMPANY_ID varchar2(1024) := '6230000';
begin
  PKG_UTIL_LOG.PROC_ETL_LOG_INSERT('PROC_ADD_PARTITION(' || nvl(P_SEGMENT_NAME,'') || ')',1);
    
  --所有分区表/参数指定的分区表
  V_SQL_PT := V_SQL_PT || 'select distinct SEGMENT_NAME ';
  V_SQL_PT := V_SQL_PT || 'from ';
  V_SQL_PT := V_SQL_PT || '  USER_SEGMENTS T1 ';
  V_SQL_PT := V_SQL_PT || 'where ';
  V_SQL_PT := V_SQL_PT || '  SEGMENT_TYPE in (''TABLE PARTITION'',''TABLE SUBPARTITION'') ';
  V_SQL_PT := V_SQL_PT || '  and (SEGMENT_NAME like ''A_%'' or SEGMENT_NAME like ''F_%'' or SEGMENT_NAME like ''ODS_%'')';
  if (P_SEGMENT_NAME is not null) then
    V_SQL_PT := V_SQL_PT || 'and SEGMENT_NAME = ''' || P_SEGMENT_NAME || '''';
  end if;

  --分区类型
  V_SQL_PTT := 'select distinct SEGMENT_TYPE from USER_SEGMENTS where SEGMENT_NAME = :BV_1';

  --分区表的所有子分区
  V_SQL_SPT := V_SQL_SPT || 'select PARTITION_NAME ';
  V_SQL_SPT := V_SQL_SPT || 'from ';
  V_SQL_SPT := V_SQL_SPT || '  USER_SEGMENTS T1 ';
  V_SQL_SPT := V_SQL_SPT || 'where ';
  V_SQL_SPT := V_SQL_SPT || '  SEGMENT_NAME = :BV_1 ';
  V_SQL_SPT := V_SQL_SPT || '  and PARTITION_NAME like ''%_OTHERS'' ';

  --所有分区表循环
  open C_PT for V_SQL_PT;
  loop
  	fetch C_PT into V_SEGMENT_NAME;
    exit when C_PT%notfound;

    dbms_output.put_line(V_SEGMENT_NAME || ' 分区重建开始...');

    --分区类型
    execute immediate V_SQL_PTT into V_SEGMENT_TYPE using V_SEGMENT_NAME;
    
    dbms_output.put_line('  ' || V_SEGMENT_NAME || ' 分区类型为 ' || V_SEGMENT_TYPE);
    
    --分区
    if V_SEGMENT_TYPE = 'TABLE PARTITION' then

      V_SQL_PT_SPLIT := '';
      V_SQL_PT_SPLIT := V_SQL_PT_SPLIT || 'alter table ' || V_SEGMENT_NAME || ' ';
      V_SQL_PT_SPLIT := V_SQL_PT_SPLIT || 'split partition P_OTHERS values (' || V_COMPANY_ID || ') into ( ';
      V_SQL_PT_SPLIT := V_SQL_PT_SPLIT || '  partition P_' || V_COMPANY_ID || ',';
      V_SQL_PT_SPLIT := V_SQL_PT_SPLIT || '  partition P_OTHERS ';
      V_SQL_PT_SPLIT := V_SQL_PT_SPLIT || ') ';
      V_SQL_PT_SPLIT := V_SQL_PT_SPLIT || 'update indexes ';

      execute immediate V_SQL_PT_SPLIT;
      
      dbms_output.put_line('  新建分区: P_' || V_COMPANY_ID);
      
    --子分区
    elsif V_SEGMENT_TYPE = 'TABLE SUBPARTITION' then
    
      --创建新子分区
      open C_SPT for V_SQL_SPT using V_SEGMENT_NAME;
      loop
      	fetch C_SPT into V_PARTITION_NAME;
        exit when C_SPT%notfound;

        V_SQL_SPT_SPLIT := '';
        V_SQL_SPT_SPLIT := V_SQL_SPT_SPLIT || 'alter table ' || V_SEGMENT_NAME || ' ';
        V_SQL_SPT_SPLIT := V_SQL_SPT_SPLIT || 'split subpartition ' || V_PARTITION_NAME || ' values (' || V_COMPANY_ID || ') into ( ';
        V_SQL_SPT_SPLIT := V_SQL_SPT_SPLIT || '  subpartition ' || substr(V_PARTITION_NAME,1,instr(V_PARTITION_NAME,'_SP_')) || 'SP_' || V_COMPANY_ID || ',';
        V_SQL_SPT_SPLIT := V_SQL_SPT_SPLIT || '  subpartition ' || V_PARTITION_NAME || ' ';
        V_SQL_SPT_SPLIT := V_SQL_SPT_SPLIT || ') ';
        V_SQL_SPT_SPLIT := V_SQL_SPT_SPLIT || 'update indexes ';
        
        execute immediate V_SQL_SPT_SPLIT;
        
        dbms_output.put_line('  新建子分区: ' || substr(V_PARTITION_NAME,1,instr(V_PARTITION_NAME,'_SP_')) || 'SP_' || V_COMPANY_ID);
        
      end loop;
      close C_SPT;  
      
      --子分区模板
      V_SQL_SPT_TMP := V_SQL_SPT_TMP || 'alter table ' || V_SEGMENT_NAME || ' ';
      V_SQL_SPT_TMP := V_SQL_SPT_TMP || 'set subpartition template ( ';
      V_SQL_SPT_TMP := V_SQL_SPT_TMP || '  subpartition SP_2000000 values (2000000), ';
      V_SQL_SPT_TMP := V_SQL_SPT_TMP || '  subpartition SP_6280000 values (6280000), ';
      V_SQL_SPT_TMP := V_SQL_SPT_TMP || '  subpartition SP_6010000 values (6010000), ';
      V_SQL_SPT_TMP := V_SQL_SPT_TMP || '  subpartition SP_6020500 values (6020500), ';
      V_SQL_SPT_TMP := V_SQL_SPT_TMP || '  subpartition SP_6050000 values (6050000), ';
      V_SQL_SPT_TMP := V_SQL_SPT_TMP || '  subpartition SP_6070000 values (6070000), ';
      V_SQL_SPT_TMP := V_SQL_SPT_TMP || '  subpartition SP_6080400 values (6080400), ';
      V_SQL_SPT_TMP := V_SQL_SPT_TMP || '  subpartition SP_6090000 values (6090000), ';
      V_SQL_SPT_TMP := V_SQL_SPT_TMP || '  subpartition SP_6110000 values (6110000), ';
      V_SQL_SPT_TMP := V_SQL_SPT_TMP || '  subpartition SP_6170000 values (6170000), ';
      V_SQL_SPT_TMP := V_SQL_SPT_TMP || '  subpartition SP_6200000 values (6200000), ';
      V_SQL_SPT_TMP := V_SQL_SPT_TMP || '  subpartition SP_6300000 values (6300000), ';
      V_SQL_SPT_TMP := V_SQL_SPT_TMP || '  subpartition SP_6250000 values (6250000), ';
      V_SQL_SPT_TMP := V_SQL_SPT_TMP || '  subpartition SP_6130000 values (6130000), ';
      V_SQL_SPT_TMP := V_SQL_SPT_TMP || '  subpartition SP_6140000 values (6140000), ';
      V_SQL_SPT_TMP := V_SQL_SPT_TMP || '  subpartition SP_6160000 values (6160000), ';
      V_SQL_SPT_TMP := V_SQL_SPT_TMP || '  subpartition SP_6180000 values (6180000), ';
      V_SQL_SPT_TMP := V_SQL_SPT_TMP || '  subpartition SP_6230000 values (6230000), ';
      V_SQL_SPT_TMP := V_SQL_SPT_TMP || '  subpartition SP_OTHERS values (default) ';
      V_SQL_SPT_TMP := V_SQL_SPT_TMP || ') ';

      execute immediate V_SQL_SPT_TMP;
      dbms_output.put_line('  更新子分区模板');
     
    end if;

    dbms_output.put_line(V_SEGMENT_NAME || ' 分区重建完毕' || chr(10) || chr(10));

  end loop;
  close C_PT;

  --log:完成
  GV_LOC_STOP := 'Fin';
  PKG_UTIL_LOG.PROC_ETL_LOG_UPDATE('PROC_ADD_PARTITION(' || nvl(P_SEGMENT_NAME,'') || ')',2,GV_LOC_STOP);
  --log:失败
  exception
  when others then
    PKG_UTIL_LOG.PROC_ETL_LOG_UPDATE('PROC_ADD_PARTITION(' || nvl(P_SEGMENT_NAME,'') || ')',3,GV_LOC_STOP);

end;