CODEWITHSUNDEEP
sqloracle-databasedaterangedatabase-partitioning
mysticfalls
mysticfalls

Reputation: 455

generate list of partition name

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.

Upvotes: 0

Views: 292

Answers (2)

APC
APC

Reputation: 146199

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;

Upvotes: 2

Colin &#39;t Hart
Colin &#39;t Hart

Reputation: 7729

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

Upvotes: 2

Related Questions