Skip to Main Content
  • Questions
  • Automate to partition creation with procedure

Breadcrumb

Question and Answer

Chris Saxon

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

You Asked

We want to create a procedure X which adds a partition to a partitioned table Y every month, and then the last month data from table Y1 is inserted into Y( we are not doing exchange partitions due to some business conditions, the table Y is being inserted records via a ODI12c).

and Connor said...

Firstly, look at INTERVAL partitioning. You might not need to do any creation at all - the database will do it automatically for you !

http://docs.oracle.com/database/121/VLDBG/GUID-C121EA1B-2725-4464-B2C9-EEDE0C3C95AB.htm

If you cant use intervals, then its just a case executing 'alter table add partition' based on what you currently have. Here's an example:

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.



Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanx you

Lilantha Lakmal, October 26, 2016 - 8:30 am UTC

Your answer is very use full to me, Thanx you

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.