oracle分区表split,oracle split分区脚本-Go语言中文社区

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;