5

I have a table in oracle:

CREATE TABLE transaction (
    id INT NOT NULL,
    accountnumber VARCHAR NOT NULL,
    stmtvaluedate DATE NOT NULL,
    ...
)

And I want to partition this table by the stmtvaluedate column. My goal is to create a new partition after a month have passed.

Is there any good script, for it? Or I have to create static numbers of partitions?

The best would be: if a month have passed, a new partition will be created automatically.

Can anyone give me an example about how to partition a table by a date column after every month? If the automatically partitioning is impossible, than I would need an example, which creates partitions to a year from now by a date column, about every month.

Thanks!

victorio
  • 6,224
  • 24
  • 77
  • 113
  • [this](http://docs.oracle.com/cd/E18283_01/server.112/e16541/part_admin001.htm#BAJHFFBE) could helps. You can use Interval-Partitioned Table. But it works only for Oracle 11.1 and upper – Tatiana Nov 19 '15 at 15:59
  • Thank you. And can I partition by **week** or **day** too? – victorio Nov 20 '15 at 11:27

1 Answers1

17

What you want to do is completely possible. This should do it:

CREATE TABLE transaction (
    id INT NOT NULL,
    accountnumber VARCHAR2(30) NOT NULL,
    stmtvaluedate DATE NOT NULL
)
PARTITION BY RANGE (stmtvaluedate)
INTERVAL (NUMTOYMINTERVAL (1,'MONTH')) 
    ( partition transaction_old values less than (to_date('01-JAN-2000','DD-MON-YYYY') ));
Matthew McPeak
  • 17,705
  • 2
  • 27
  • 59
  • 1
    And what if I want to partition a table not by mounth, but by a week, or a day? Are these rigth?: **INTERVAL (NUMTOYMINTERVAL (1,'WEEK'))** or **INTERVAL (NUMTOYMINTERVAL (1,'DAY'))** And what will clause the date 01-JAN-2000? Does it mean, if a *stmtvaluedate* value is less than 2000.01.01, than that row will go into the partition? – victorio Nov 20 '15 at 09:55
  • 1
    Use `NUMTODSINTERVAL` for days and weeks. And yes, the 1/1/2000 clause is to tell Oracle to put all the data before a given date into a single partition. If you have a lot of historical data, that date may not be appropriate. – Matthew McPeak Nov 20 '15 at 13:45