Oracle自动创建表分区

Oracle的分区表大家应该都不陌生，分区表有范围分区，列表分区，HASH分区及组合分区4种。其中，范围分区应用的最为广泛，列表次之。

范围分区有几个关键字，一个是partition by range，表示分区为范围分区；values less than 是范围分区的特定语法，指明具体的范围。

在生产应用中，我们经常会根据年份或者月份去创建范围分区。目前有个客户，他们需要根据月份做一个范围分区表，但是现在他们要每到月底，需要手动去创建一个分区。有时候因为遗忘可能导致新的数据进来没有对应的分区而报错。因此希望通过一个自动脚本，定时自动创建这个分区。

大致思路：创建一个存储过程，这个存储过程根据日期，对字符串进行操作，生成对应的分区表名，然后创建一个作业，到月底最后一天的晚上10点执行。

首先，分区表的创建语句如下：

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，如果每个分区的命名规则不是这样，需要修改字符串拼接那部分。

转自 http://www.learnfuture.com/article/1706