Oracle表自动分区

Oracle分区表是需要一开始建立的，需备份下原表，再建立分区表，如:

create table TEST1
 (
   ID         VARCHAR2(50) default sys_guid() not null,
   COLA       VARCHAR2(10),
   COLB       VARCHAR2(20),
   COLC       VARCHAR2(20),
   COLD       VARCHAR2(20),
   CREATEDATE DATE default sysdate
 ) partition by range(createdate)
   (PARTITION P19910107 VALUES LESS THAN (TO_DATE('1991-01-07', 'YYYY-MM-DD')), 
    PARTITION PN VALUES LESS THAN (MAXVALUE));

Oracle表分区不会自动进行，需要配合job任务和存储过程来完成，如

create or replace procedure p_addpatition(tab varchar2,flag varchar2)
is
sweek1 date;--开始时间的下周一
nweek1 varchar(50);
parti varchar(50);
nowweek1 date;--现在时间的下周一
-----------------
smonth1 date;
nmonth1 varchar(50);
nowmonth1 date;
---------------------
syear1 date;
nyear1 varchar(50);
nowyear1 date;
-------------------
sdate date;
v_sql varchar2(4000);
 begin


   if flag='week' then --按周分区
     begin

        v_sql:='select to_char(trunc(to_date(max(substr(partition_name,2)),''yyyymmdd''),''iw'') + 7,''yyyymmdd'') from user_tab_partitions where table_name='''||tab||''' and partition_name<>''PN''';
        dbms_output.put_line(v_sql);
       execute immediate v_sql  into nweek1;
       --sweek1:=to_date(nweek1,'yyyy-mm-dd');
        dbms_output.put_line(nweek1);

       parti:='P'||nweek1;--分区名
        dbms_output.put_line(parti);

       sweek1:=to_date(nweek1,'yyyy-mm-dd');
       dbms_output.put_line(sweek1);

       select trunc(sysdate,'iw') + 7 into nowweek1 from dual;
        dbms_output.put_line(nowweek1);

       while sweek1<=nowweek1 loop
           v_sql:='';
           v_sql:='alter table '||tab||' split partition PN  at(to_date('''||nweek1||''',''yyyymmdd'')) into (partition '||parti||',partition PN) update global indexes';
           --dbms_output.put_line(v_sql);
           execute immediate v_sql;
           sweek1:=sweek1+7;
           nweek1:=to_char(sweek1,'yyyymmdd');
           parti:='P'||to_char(sweek1,'yyyymmdd');--分区名
       end loop;
     end;
   elsif flag='month' then --按月分区
     begin
         v_sql:='select to_char(add_months(to_date(max(substr(partition_name,2)),''yyyymmdd''),1),''yyyymmdd'') from user_tab_partitions where table_name='''||tab||''' and partition_name<>''PN''';
        dbms_output.put_line(v_sql);
       execute immediate v_sql  into nmonth1;
        dbms_output.put_line(nmonth1);

       parti:='P'||nmonth1;--分区名
        dbms_output.put_line(parti);

       smonth1:=to_date(nmonth1,'yyyy-mm-dd');
       dbms_output.put_line(smonth1);

       select add_months(sysdate,1)  into nowmonth1 from dual;
        dbms_output.put_line(nowmonth1);

       while smonth1<=nowmonth1 loop
           v_sql:='';
           v_sql:='alter table '||tab||' split partition PN  at(to_date('''||nmonth1||''',''yyyymmdd'')) into (partition '||parti||',partition PN) update global indexes';
           --dbms_output.put_line(v_sql);
           execute immediate v_sql;
           smonth1:=add_months(smonth1,1);
           nmonth1:=to_char(smonth1,'yyyymmdd');
           parti:='P'||to_char(smonth1,'yyyymmdd');--分区名
       end loop;
     end;
   elsif flag='year' then --按年分区
     begin
         v_sql:='select to_char(add_months(to_date(max(substr(partition_name,2)),''yyyymmdd''),12),''yyyymmdd'') from user_tab_partitions where table_name='''||tab||''' and partition_name<>''PN''';
        dbms_output.put_line(v_sql);
       execute immediate v_sql  into nyear1;
        dbms_output.put_line(nyear1);

       parti:='P'||nyear1;--分区名
        dbms_output.put_line(parti);

       syear1:=to_date(nyear1,'yyyy-mm-dd');
       dbms_output.put_line(syear1);

       select add_months(sysdate,1)  into nowmonth1 from dual;
        dbms_output.put_line(nowmonth1);

       while syear1<=nowmonth1 loop
           v_sql:='';
           v_sql:='alter table '||tab||' split partition PN  at(to_date('''||nyear1||''',''yyyymmdd'')) into (partition '||parti||',partition PN) update global indexes';
           --dbms_output.put_line(v_sql);
           execute immediate v_sql;
           syear1:=add_months(syear1,12);
           nyear1:=to_char(syear1,'yyyymmdd');
           parti:='P'||to_char(syear1,'yyyymmdd');--分区名
       end loop;
     end;
   end if;
 end;

job如下

declare
job number;
BEGIN
DBMS_JOB.SUBMIT(
JOB => job, /自动生成JOB_ID/
WHAT => ‘proc_add_test;’, /需要执行的存储过程名称或SQL语句/
NEXT_DATE => sysdate+3/(2460), /初次执行时间-下一个3分钟/ INTERVAL => ‘trunc(sysdate,”mi”)+1/(2460)’ /每隔1分钟执行一次/
);
commit;
end;