CODEWITHSUNDEEP
oracle-databasedatatablepartitioningcreate-table
lia
lia

Reputation: 193

add interval monthly partition

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?

Upvotes: 0

Views: 286

Answers (1)

Popeye
Popeye

Reputation: 35900

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

Upvotes: 1

Related Questions