DROP TABLE gaga1;

CREATE TABLE gaga1 (abc TIMESTAMP)
PARTITION BY RANGE (abc)INTERVAL ( NUMTODSINTERVAL (1, 'DAY') )( PARTITION OLD_DATA VALUES LESS THAN (TIMESTAMP '2015-01-01 00:00:00.000000'));


INSERT INTO gaga1 select to_timestamp('31/12/9999 23:59:59.000000','DD/MM/YYYY HH24:MI:SS.FF') from dual ;

ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions

I hope the universe lasts a bit longer than another 8000 years ;)

When you insert a new partition key value in an interval partitioned table, the database needs to "make room" for all the partitions from this value to the current lower value.

In this case that's:

select date'9999-12-31' - date'2015-01-01' days
from   dual;

DAYS      
  2,916,460 

Nearly 3 million partitions. About three times the upper limit for the number of partitions (2**20 ~ 1 million)!

Some workarounds:

Move the lower or upper bounds

I'm guessing you can't move the lower bound much earlier than 1 Jan 2015. So you'll need to move your "end of the universe" much closer, to around the year 4,000!

In any case, note you can't use the value 31 Dec 9999 for "the end".

Because if you do, the database needs to set the upper bound for the last partition to 1 Jan 10,000. An impossible date in Oracle Database!

Change the granularity

Instead of daily partitions, you use weekly (7 day) you've got enough room to store all the dates you want:

CREATE TABLE gaga1 (abc TIMESTAMP)
  PARTITION BY RANGE (abc) 
  INTERVAL ( NUMTODSINTERVAL (7, 'DAY') ) ( 
    PARTITION OLD_DATA VALUES LESS THAN (TIMESTAMP '2015-01-01 00:00:00.000000')
  );

INSERT INTO gaga1 select to_timestamp('29/12/9999 23:59:59.000000','DD/MM/YYYY HH24:MI:SS.FF') from dual;

SELECT COUNT(*) FROM gaga1;

COUNT(*)   
          1 

Though note here the max value moves back to 29 Dec 9999.

Change the granularity & subpartition

If you must have daily partitions, you could partition by month/week/... Then subpartition by day.

Interval subpartitions are currently unsupported. Instead you can:

- Add a virtual column extracting the day number from the date
- Subpartition by this VC
- Define a subpartition template, which creates a subpartition for each possible day for the top-level granularity

e.g.:

CREATE TABLE gaga1 (
  abc TIMESTAMP,
  abc_day INT as (extract (day from abc) )
) PARTITION BY RANGE (abc) 
  INTERVAL ( INTERVAL '1' MONTH )
  SUBPARTITION BY LIST ( abc_day ) 
  SUBPARTITION TEMPLATE (
    subpartition sp1 values ( 1 ),
    subpartition sp2 values ( 2 )
    -- etc.
  )  ( 
    PARTITION OLD_DATA VALUES LESS THAN (TIMESTAMP '2015-01-01 00:00:00.000000')
  );