oracle自动分区重命名.txt

create or replace procedure prc_updat_partname(v_tablename varchar2,v_type varchar2) is
    v_sum       Number;
    v_str       varchar2(2000);
    v_types     varchar2(8);
    V_COL       varchar2(20);
    V_RESTR    varchar2(2000);
    V_RENAME   VARCHAR2(10);
begin
    if v_type = '1' then
      v_types:='YYYYMMDD';
    ELSIF v_type='2' then 
      v_types:='YYYYMM';
    ELSIF v_type='3' then 
      v_types:='YYYY';
    else
      v_types:='YYYYMM';
    end if;
    
    --dbms_output.put_line(v_types);
    execute immediate 'SELECT COLUMN_NAME FROM user_PART_KEY_COLUMNS t where  NAME=upper('''||v_tablename||''')' into V_COL;
    
    for idx in (select table_name,partition_name from user_tab_partitions t  where t.table_name = v_tablename and partition_name like 'SYS%') loop
      
      V_STR:='SELECT count(1) FROM  '||v_tablename||' partition('||idx.partition_name||') where rownum=1';
      execute immediate V_STR into v_sum;
      if v_sum=1 then 
         V_STR:='SELECT TO_CHAR('||V_COL||','''||v_types||''') FROM  '||v_tablename||' partition('||idx.partition_name||') where rownum=1';
         execute immediate V_STR into V_RENAME;
      
         V_RESTR:='alter table '||v_tablename||'  rename partition '||idx.partition_name||' to P'||V_RENAME;