Thanks for the question, Lilantha.
Asked: October 24, 2016 - 5:35 am UTC
Last updated: October 24, 2016 - 6:22 am UTC
Version: 12c
Viewed 10K+ times! This question is
SQL> create table par_demo 2 ( d date, z int ) 3 partition by range ( d ) 4 ( 5 partition p_201512 values less than ( date '2016-01-01' ), 6 partition p_201601 values less than ( date '2016-02-01' ), 7 partition p_201602 values less than ( date '2016-03-01' ) 8 ); Table created. SQL> set serverout on SQL> declare 2 l_long long; 3 l_hi_date date; 4 l_next_date date; 5 l_ddl long; 6 begin 7 select high_value 8 into l_long 9 from user_tab_partitions 10 where table_name = 'PAR_DEMO' 11 and partition_position = 12 ( select max(partition_position) 13 from user_tab_partitions 14 where table_name = 'PAR_DEMO' ); 15 16 execute immediate 'select '||l_long||' from dual' into l_hi_date; 17 18 l_next_date := add_months(l_hi_date,1); 19 20 dbms_output.put_line('New upper bound is '||l_next_date); 21 22 l_ddl := 23 'alter table par_demo add partition p_'||to_char(l_hi_date,'YYYYMM')||' values less than ( date '''||to_char(l_next_date,'YYYY-MM-DD')||''')'; 24 25 dbms_output.put_line(l_ddl); 26 execute immediate l_ddl; 27 end; 28 / New upper bound is 01-APR-16 alter table par_demo add partition p_201603 values less than ( date '2016-04-01') PL/SQL procedure successfully completed. SQL> / New upper bound is 01-MAY-16 alter table par_demo add partition p_201604 values less than ( date '2016-05-01') PL/SQL procedure successfully completed. SQL> / New upper bound is 01-JUN-16 alter table par_demo add partition p_201605 values less than ( date '2016-06-01') PL/SQL procedure successfully completed.
Lilantha Lakmal, October 26, 2016 - 8:30 am UTC
If you are new to partitioning, check out Connor McDonald's introduction series here.
Documentation set on VLDB and Partitioning.