Hi,
I had some observation regarding Interval partitioning when I was looking into one issue,
Below is the use case
For ex : We have two tables
CREATE TABLE TEST_GURU_1
(
ENAME VARCHAR2(500),
EMPDATE DATE
)
PARTITION BY RANGE (EMPDATE)
(
PARTITION BUS_DATE_032003 VALUES LESS THAN (TO_DATE(' 2003-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')));
CREATE TABLE TEST_GURU
(
ENAME VARCHAR2(500),
EMPDATE DATE
)
PARTITION BY RANGE (EMPDATE)
INTERVAL( NUMTOYMINTERVAL(1, 'MONTH'))
(
PARTITION BUS_DATE_032003 VALUES LESS THAN (TO_DATE(' 2003-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')));
TEST_GURU_1 table is created without interval partition
TEST_GURU table is created with interval partition
As of now table is empty ,I fire below queries
Greater than 15-dec-9999
SELECT * FROM TEST_GURU_1
where EMPDATE > to_date('12/15/9999','mm/dd/yyyy') ;
--This works fine and gives 0 rows
SELECT * FROM TEST_GURU
where EMPDATE > to_date('12/15/9999','mm/dd/yyyy') ;
--This works fine and gives 0 rows
--Greater than 16-dec-9999
SELECT * FROM TEST_GURU_1
where EMPDATE > to_date('12/16/9999','mm/dd/yyyy') ;
--This works fine and gives 0 rows(For table without interval partition)
SELECT * FROM TEST_GURU
where EMPDATE > to_date('12/16/9999','mm/dd/yyyy') ;
--This throws error
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
I wanted to know why this behavior only for the Interval partitioned table.
Just curious about the issue here.
Regards
Gururaj
I suspect its a bug that has been fixed, because I cannot reproduce that on my 12.2 or 18c instance.
SQL> CREATE TABLE TEST_GURU_1
2 (
3 ENAME VARCHAR2(500),
4 EMPDATE DATE
5 )
6 PARTITION BY RANGE (EMPDATE)
7 (
8 PARTITION BUS_DATE_032003 VALUES LESS THAN (TO_DATE(' 2003-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')));
Table created.
SQL>
SQL>
SQL>
SQL> CREATE TABLE TEST_GURU
2 (
3 ENAME VARCHAR2(500),
4 EMPDATE DATE
5 )
6 PARTITION BY RANGE (EMPDATE)
7 INTERVAL( NUMTOYMINTERVAL(1, 'MONTH'))
8 (
9 PARTITION BUS_DATE_032003 VALUES LESS THAN (TO_DATE(' 2003-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')));
Table created.
SQL>
SQL> SELECT * FROM TEST_GURU_1
2 where EMPDATE > to_date('12/15/9999','mm/dd/yyyy') ;
no rows selected
SQL>
SQL> SELECT * FROM TEST_GURU
2 where EMPDATE > to_date('12/15/9999','mm/dd/yyyy') ;
no rows selected
SQL>
SQL> SELECT * FROM TEST_GURU_1
2 where EMPDATE > to_date('12/16/9999','mm/dd/yyyy') ;
no rows selected
SQL>
SQL> SELECT * FROM TEST_GURU
2 where EMPDATE > to_date('12/16/9999','mm/dd/yyyy') ;
no rows selected
So perhaps get in touch with Support and see if there is a patch for your platform