SQL Language (MOSC)

MOSC Banner

Oracle Database version 12.2 and higher can't use "TRANSITION" keyword for interval

In Oracle Database version 12.1 I could specify "TRANSITION" keyword make database aware of after which base partition the interval partitions should be created.

So that if I missed partition for VALUES LESS THAN (TO_DATE('01-03-2022', 'DD-MM-YYYY')), it will be created automatically.

CREATE TABLE interval_sales

  ( prod_id    NUMBER(6)

  , cust_id    NUMBER

  , time_id    DATE

  , channel_id   CHAR(1)

  , promo_id    NUMBER(6)

  , quantity_sold NUMBER(3)

  , amount_sold  NUMBER(10,2)

  ) 

 PARTITION BY RANGE (time_id) 

 INTERVAL(NUMTOYMINTERVAL(1, 'MONTH')) TRANSITION ("P0") 

  ( PARTITION p0 VALUES LESS THAN (TO_DATE('01-01-2022', 'DD-MM-YYYY')),

   PARTITION p1 VALUES LESS THAN (TO_DATE('01-02-2022', 'DD-MM-YYYY')),

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center