generate list of partition name

Question

I would like generate a list of partition without having to manually type them and I can also choose how many partition I want to create. I'm using partition by range. Please see below example.

P_PARTITION_20120830 VALUES LESS THAN ('2012-08-30')
P_PARTITION_20120930 VALUES LESS THAN ('2012-09-30')
P_PARTITION_20121030 VALUES LESS THAN ('2012-10-30')
...
and so on

This is what I have come up so far.

select  'P_PARTITION_' || to_char(add_months(sysdate, 1),'YYYYMMDD')
|| ' VALUES LESS THAN ' || to_char(sysdate, '(''YYYY-MM-DD'')') 
from dual;

Any feedback would be appreciated. Thank you.

APC · Accepted Answer · 2012-07-26T08:17:59.917

2

This will generate partitions names for twelve months. Adjust the bounds in the CONNECT BY clause to produce the number you need.

select  'P_PARTITION_' || to_char(add_months(sysdate, level),'YYYYMMDD') 
|| ' VALUES LESS THAN ' || to_char(sysdate, 'YYYY-MM-DD')  
from dual
connect by level <= 12;

edited Jul 26 '12 at 08:17

answered Jul 26 '12 at 07:49

APC

144,005
19
170
281

score 2 · Answer 2 · answered Jul 26 '12 at 07:58

Which version of Oracle are you using? If you are using 11g you can get Oracle to create partitions for you as needed. This means less work for you the DBA.

Oracle calls this "Interval Partitioning".

See http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_7002.htm#BABJDACD for the syntax, while for an example see http://docs.oracle.com/cd/E11882_01/server.112/e25523/part_admin001.htm#autoId3

generate list of partition name

2 Answers2