d_next_dat := add_months(trunc(d_last_part_dat, 'Q'),
3 * p_interval_num);
elsif p_part_iden in ('y', 'Y') then
d_next_dat := add_months(trunc(d_last_part_dat, 'Q'),
12 * p_interval_num);
else
raise_application_error(- 20999,
'Type following identifier with p_part_iden : d(day) , m(month) , q(quarter) , y(year) .');
end if ;
--根据截止时间拼装分区名称v_part_name 以及 less than 子句v_part_less_than_val
--example: v_part_name := ORDERS_20120101
-- v_part_less_than_val := TO_DATE(TO_CHAR(20120101,'YYYYMMDD'))
v_part_name := upper(p_tb_name) || '_' ||
to_char(d_next_dat, 'yyyymmdd');
v_part_less_than_val := 'to_date(' || to_char(d_next_dat, 'yyyymmdd') ||
',''yyyymmdd'')';
--拼装的添加分区的sql语句v_add_part_sql
-- example : ALTER TABLE print_media_part ADD PARTITION p4 VALUES LESS THAN (400)
v_add_part_sql := 'ALTER TABLE '
|| p_tb_name
|| ' ADD PARTITION '
|| v_part_name
|| ' VALUES LESS THAN ('
|| v_part_less_than_val
|| ')';
execute immediate v_add_part_sql;
exception
when others then
err_log( 'dba_autoextend_partitions', sqlcode , sqlerrm);
end;
end dba_tools;
创建测试表
CREATE TABLE orders
( prod_id NUMBER(6)
, order_time DATE
, quantity NUMBER(3)
, price NUMBER(10,2)
)
tablespace users
PARTITION BY RANGE (order_time)
( PARTITION order_before VALUES LESS THAN (TO_DATE('20100101','yyyymmdd'))
, PARTITION order_20100101 VALUES LESS THAN (TO_DATE('20110101','yyyymmdd'))
, PARTITION order_20110101 VALUES LESS THAN (TO_DATE('20120101','yyyymmdd'))
, PARTITION order_20120101 VALUES LESS THAN (TO_DATE('20130101','yyyymmdd'))
);
测试：
SQL> select TABLE_NAME,PARTITION_NAME,HIGH_VALUE from user_tab_partitions where table_name='ORDERS' ;
TABLE_NAME PARTITION_NAME HIGH_VALUE
------------------------------ ------------------------------ --------------------------------------------------------------------------------
ORDERS ORDER_20100101 TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ORDERS ORDER_20110101 TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ORDERS ORDER_20120101 TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ORDERS ORDER_BEFORE TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SQL> exec dba_tools.dba_autoextend_partitions('ORDERS','y',2) ;
PL/SQL procedure successfully completed
SQL> select TABLE_NAME,PARTITION_NAME,HIGH_VALUE from user_tab_partitions where table_name='ORDERS' ;
TABLE_NAME PARTITION_NAME HIGH_VALUE
------------------------------ ------------------------------ --------------------------------------------------------------------------------
ORDERS ORDERS_20140101 TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ORDERS ORDER_20100101 TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ORDERS ORDER_20110101 TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALEND