oracle自动创建表分区

　　客户有一个需求，在每月的最后一天，为某张分区表自动创建一个分区，分区表的创建语句如下：

-- Create table
create table PAR_TEST
(
  LOANTYPE   VARCHAR2(8),
  REFNO      VARCHAR2(25),
  CUSTCOD    VARCHAR2(12),
  BRANCH     VARCHAR2(6),
  FLSTSCD    VARCHAR2(16),
  LNCCY      VARCHAR2(3),
  VSPREAD    NUMBER,
  DFTYPE     VARCHAR2(4000),
  A23ACIT    VARCHAR2(13),
  MAP_GL     VARCHAR2(13),
  TSDATE     DATE,
  CCY_TYPE   CHAR(1)
)
partition by range (TSDATE)
(
  partition DPT1 values less than (TO_DATE(' 2015-07-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace CRMDATA
    pctfree 10
    initrans 1
    maxtrans 255,
  partition DPT2 values less than (TO_DATE(' 2015-08-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace CRMDATA
    pctfree 10
    initrans 1
    maxtrans 255,
  partition DPT3 values less than (TO_DATE(' 2015-09-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace CRMDATA
    pctfree 10
    initrans 1
    maxtrans 255
);

　　根据我们对分区表的了解，多加一个分区的语句如下：

alter table PAR_TEST add partition DPTxx values less than (to_date('2017-01-31 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN'))
tablespace CRMDATA pctfree 10 initrans 1 maxtrans 255;

　　所以根据创建分区的语句，编写如下自动创建的存储过程：

create or replace procedure add_newpartitions
as
cursor c_parts is
select max(partition_name) as part_name from user_tab_partitions where table_name='PAR_TEST' group by table_name; --捕捉表最大分区的分区表名

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

begin
for i in c_parts loop
v_pname := i.part_name; --将刚才捕捉到的表名赋值给该变量

dbms_output.put_line('v_pname:'); --代码调试打印，需要打开set serveroutput on才能看到结果
dbms_output.put_line(v_pname);

v_npart := substr(v_pname,instr(v_pname,'T')+1); --用字母T截取表名，获得DPTxx的T后面的数字xx

v_newp  := last_day(last_day(sysdate)+1); --取到下个月的最后一天
dbms_output.put_line(v_newp);

v_pname := substr(v_pname,1,instr(v_pname,'T'))||to_char(to_number(v_npart)+1); --字符串拼接，拼接出将要创建的分区表的表名
dbms_output.put_line('v_pname:');
dbms_output.put_line(v_pname);

v_sql := 'alter table DW_DPDAILY add partition '||v_pname||' values less than ('||'to_date('''||to_char(v_newp,'yyyy-mm-dd')||' 00:00:00'','''||'SYYYY-MM-DD HH24:MI:SS'||''',''NLS_CALENDAR=GREGORIAN'')'||')'||' tablespace CRMDATA pctfree 10 initrans 1 maxtrans 255';

dbms_output.put_line(v_sql);
execute immediate v_sql;
end loop;
end;

　　有了次存储过程，需要定期执行，因此就需要创建一个JOB，让这个存储过程定期被调用执行，具体如下：

DECLARE
v_job number;
begin
dbms_job.submit(
job => v_job,
what => 'add_newpartitions;',
next_date => trunc(last_day(SYSDATE))+22/24, --每月最后一天的晚上10点执行
interval => 'trunc(last_day(add_months(SYSDATE,1)))+22/24'); --下个月的最后一天晚上10点执行
commit;  
end;
/

　　注：此脚本创建分区表的格式为DPT20，如果每个分区的命名规则不是这样，需要修改字符串拼接那部分。