oracle split分区脚本

declare 
v_sql varchar2(1000);
BEGIN
    FOR outcur IN ( 
      select '20180719' partition_time,'GSCS_IIGLOG' as table_name from dual union all
      select '20180719' partition_time,'SFCS_SSLOG' as table_name from dual union all
      select '20180719' partition_time,'POCS_PINGPGLOG' from dual  
     ) 
    LOOP   

        FOR cur IN ( select  dt.table_name,'P'||to_char( to_date(p2,'yyyymmddhh24') +2/24,'yyyymmddhh24')  as P1
                         , 'P'|| to_char(to_date(dt.p2,'yyyymmddhh24') +1/24,'yyyymmddhh24') as  p2
                     from (
                        select b.table_name
                        ,b.dates|| lpad( a.hours,2,0) as P2
                          from 
                          (with x as
                            ( select 'mengl' chr from dual)
                            select level-1 as hours  from x connect by level <= 24
                            order by 1 desc) a,(  SELECT outcur.table_name as table_name , outcur.partition_time dates from dual) b
                         ) dt
             )
          LOOP
            BEGIN
             v_sql:=' alter table '|| cur.table_name||' split partition '|| cur.p1||' at(TIMESTAMP'''|| to_char(to_date(substr(cur.p2,2),'yyyymmddhh24'),'yyyy-mm-dd hh24:mi:ss') ||''') into (
                      partition '|| cur.p2||' ,partition '|| cur.p1||'  ) ';

      execute immediate(v_sql); 
     -- dbms_output.put_line(v_sql); 
      EXCEPTION WHEN OTHERS THEN
        null;
        
            END;
          END LOOP;
 END LOOP;     
    
END;
 

 

打印出来看看呢：

 

 

 

改变表空间位置

declare 
v_sql varchar2(1000);
BEGIN
    FOR outcur IN ( 
      select '20180719' partition_time,'FCS_INTETIGLOG' as table_name from dual union all
      select '20180719' partition_time,'GSACS_SMOG' as table_name from dual union all
      select '20180719' partition_time,'SASFCS_PIONGOG' from dual 
     ) 
    LOOP   

      --  FOR cur IN ( select * from user_tab_partitions where table_name=outcur.table_name and partition_name like '%20180717%' and partition_name<>'P2018071800')
              FOR cur IN ( select * from user_tab_partitions where table_name=outcur.table_name  and partition_name='P2018072100')
          LOOP
            BEGIN
             v_sql:=' alter table '|| cur.table_name||' move partition '|| cur.partition_name ||' tablespace TBAPP_UNI_BMW_1DAY007 ';
                        
      execute immediate(v_sql); 
     -- dbms_output.put_line(v_sql); 
      EXCEPTION WHEN OTHERS THEN
        null;
        
            END;
          END LOOP;
 END LOOP;     
    
END;