Oracle Database INTERVAL Partitioning Date Error With 31-12-9999
Category : Knowledge
You may have seen the below error when using dates at the extremes with an Oracle Database. This post focuses on the scenario where you’re inserting a high date, such as 31-12-9999 into an INTERVAL partitioned table. High dates such as this are often used in warehouse environments where from and to dates are specified to denote a period of time the data was valid for – a high date specifying the current data row.
Take the following table and partition definition:
CREATE TABLE interval_date_test ( my_date DATE NOT NULL ) PARTITION BY RANGE (my_date) INTERVAL(NUMTOYMINTERVAL(1, 'MONTH')) ( PARTITION p_interval_date_test0 VALUES LESS THAN (TO_DATE('2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ) ENABLE ROW MOVEMENT /
Each row inserted will go into a partition for that year-month combination. This can be useful for high volume data that’s generally queried for one-or-so months at a time.
If, however, you try to insert the following row, you’ll get an error:
INSERT INTO interval_date_test (my_date) VALUES(DATE '9999-12-31');
INSERT INTO interval_date_test (my_date) VALUES(DATE '9999-12-31') * ERROR at line 1: ORA-01841: (full) year must be between -4713 and +9999, and not be 0
This is due to the way the Oracle Database defines a partition. An INTERVAL RANGE partition in an Oracle Database is defined as accepting dates LESS THAN a specific value. The below example would store any dates up to the clock ticking over into May 2016.
VALUES LESS THAN (TO_DATE('2016-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
When partitioned monthly, the LESS THAN value for inserting DATE ‘9999-12-31’ would have to be DATE ‘10000-01-01’ and that is not a valid date value. Oracle Database dates must be between the year -4713 and +9999 inclusive as explained by the error.
The solution? Either use a non-INTERVAL partition strategy, reduce the date being INSERTed or experiment with a Virtual Column and partition on that.