CODEWITHSUNDEEP
sqloracle-databasedatabase-partitioning
lia
lia

Reputation: 193

oracle range partition on timestampe column

This is what I have written to create a table with a daily partition on the createTime field

create table T_ASYNCFCMNOTIFICATION (
    C_ID NUMBER(34, 0) not null,
    C_CREATETIME timestamp not null,
    C_DEVICETOKEN varchar2(255 char),
    F_DATA NUMBER(34, 0),
    primary key (C_ID)
)
   PARTITION BY RANGE (C_CREATETIME)
INTERVAL(NUMTOYMINTERVAL(1, 'day'))
(
 PARTITION T_ASYNCFCMNOTIFICATION_P1 VALUES LESS THAN (TO_DATE('01-11-2020', 'DD-MM-YYYY'))
 );

but when I run my query I've got this error :

Error report - ORA-14752: Interval expression is not a constant of the correct type 14752. 00000 - "Interval expression is not a constant of the correct type" *Cause: You attempted to use either a nonconstant interval or an interval whose data type does not correspond to the partitioning column *Action: Use only constant expressions for the interval. If the data type of partitioning column is numeric, the interval must be a numeric constant. If the data type is of type date/time, the interval must be a constant of interval type

Upvotes: 0

Views: 587

Answers (1)

Marmite Bomber
Marmite Bomber

Reputation: 21043

Use interval NUMTODSINTERVAL(1, 'day') for a daily partitioning

this works fine

....
PARTITION BY RANGE (C_CREATETIME)
INTERVAL(NUMTODSINTERVAL(1, 'day'))
(
 PARTITION T_ASYNCFCMNOTIFICATION_P1 VALUES LESS THAN (TO_DATE('01-11-2020', 'DD-MM-YYYY'))
 );

Interval NUMTOYMINTERVAL is used for YEAR or MONTH-ly partitioning

Upvotes: 3

Related Questions