add interval monthly partition

Question

I want to create this table, with a monthly partition on the endTime column. I mean each month a partition added automatically by oracle.

create table T_CALLSESSION()  PARTITON BY RANGE (C_ENDTIME )
INTERVAL(NUMTOYMINTERVAL(1,'month'); (
    C_ID NUMBER(34, 0) not null,
    C_ENDTIME timestamp not null,
    C_STARTTIME timestamp not null,
    C_TYPE number(10,0) not null,
    F_CREATOR NUMBER(34, 0),
    F_MESSAGE_THREAD NUMBER(34, 0),
    primary key (C_ID)
);

is that works?

Answer 1

There are a few mistakes in your code.

• Table name should not contain the parenthesis ()
• The PARTITION clause must be after the declaration of columns and constraints.
• You must use the INTERVAL partition so that new partitions are automatically created.
• One partition must be created with some constant values and then after other partitions will be automatically created.

Use the following code:

create table T_CALLSESSION  (
    C_ID NUMBER(34, 0) not null,
    C_ENDTIME timestamp not null,
    C_STARTTIME timestamp not null,
    C_TYPE number(10,0) not null,
    F_CREATOR NUMBER(34, 0),
    F_MESSAGE_THREAD NUMBER(34, 0),
    primary key (C_ID)
) PARTITION BY RANGE (C_ENDTIME)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
( 
   PARTITION T_CALLSESSION_P1 VALUES LESS THAN (TO_DATE('01-06-2020', 'DD-MM-YYYY'))
);