Skip to Main Content
  • Questions
  • Observation regarding Interval partitioning

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Connor McDonald

Thanks for the question, Gururaj.

Asked: December 01, 2019 - 12:15 am UTC

Answered by: Connor McDonald - Last updated: December 05, 2019 - 2:08 pm UTC

Category: SQL - Version: Oracle 12.2.0.1.0

Viewed 100+ times

Whilst you are here, check out some content from the AskTom team: Nine Good-to-Knows about PL/SQL Error Management

You Asked

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

and we said...

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

and you rated our response

  (1 rating)

Reviews

on CODE Tags

December 02, 2019 - 11:12 am UTC

Reviewer: Rajeshwaran Jeyabal

Perhaps the closing tag of the CODE is incorrect.
that would make the above code in a formatted way to read.
Chris Saxon

Followup  

December 05, 2019 - 2:08 pm UTC

Thanks, fixed.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.