databaseoracle-databasedatedatabase-partitioningdatabase-scripts

How to partition an oracle table by a date column?

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!

Solution

  • 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') ));