Oracle 关于间隔划分的观察

Hi,

I had some observation regarding Interval partitioning when I was looking into one issue,

Below is the use case


例如: 我们有两张表

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表创建时没有间隔分区
TEST_GURU表是用间隔分区创建的


到目前为止，表为空，我在下面查询

大于15-12月-9999

SELECT * FROM TEST_GURU_1  
where EMPDATE > to_date('12/15/9999','mm/dd/yyyy') ;
复制

-工作正常，并给出0行

SELECT * FROM TEST_GURU
where EMPDATE > to_date('12/15/9999','mm/dd/yyyy') ;
复制

-工作正常，并给出0行


-大于16-12月-9999

SELECT * FROM TEST_GURU_1  
where EMPDATE > to_date('12/16/9999','mm/dd/yyyy') ;
复制

-工作正常，并给出0行(For table without interval partition)

SELECT * FROM TEST_GURU
where EMPDATE > to_date('12/16/9999','mm/dd/yyyy') ;
复制

-这引发错误
ORA-01841 :( 完整) 年必须在-4713到9999之间，而不是0


I wanted to know why this behavior only for the Interval partitioned table.


Just curious about the issue here.

Regards
Gururaj

我怀疑它已经修复了一个错误，因为我无法在12.2或18c实例上重现它。

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


复制

因此，也许可以与支持人员联系，看看您的平台是否有补丁