定时自动增加分区表

create or replace procedure auto_create_newpartitions
as
cursor c_parts is
select table_name,max(partition_name) as part_name from user_tab_partitions group by table_name;

v_tname varchar2(32);
v_pname varchar2(32);
v_sql varchar2(3999);
v_mcurr varchar2(32);
v_newp date;
v_npart varchar2(32);

begin
for i in c_parts loop
v_tname := i.table_name;
v_pname := i.part_name;

v_mcurr := to_char(sysdate,'yyyymm');
v_npart := substr(v_pname,instr(v_pname,'_')+1);

if v_mcurr = v_npart then
v_npart := to_char(add_months(to_date(v_npart,'yyyymm'),1),'yyyymm');
v_newp := add_months(to_date(v_npart,'yyyymm'),1);
v_pname := substr(v_pname,1,instr(v_pname,'_'))||v_npart;
v_sql := 'alter table '||v_tname||' add partition '||v_pname||' values less than('''||to_char(v_newp,'yyyymmddhh24miss')||''')';
dbms_output.put_line(v_newp);
dbms_output.put_line(v_sql);
execute immediate v_sql;
end if;
end loop;
end;
/

以上为自动创建分区表的存过

call auto_create_newpartitions ();
这是运行存过

variable job number;
begin
sys.dbms_job.submit(:job,
'auto_create_newpartitions;',
to_date('23-01-2011 1:00:00', 'dd-mm-yyyy hh24:mi:ss'),
'ADD_MONTHS(trunc(LAST_DAY(SYSDATE),''dd''),1)-8+1/24');
commit;
end;
/
这是生成一个job定时执行自动创建分区的存过

select job,what from user_jobs;
102 auto_create_newpartitions;
这是查询生成的job号

begin
dbms_job.run(102);
end;
/
执行该job