My oracle version is 11.2.0.4.0 - 64bit.
The steps are mentioned below:
1.
CREATE TABLE TEST_PARTITION
(FILES_ID NUMBER(10) NOT NULL ,
FILE_NAME VARCHAR2(100) NOT NULL,
SRC_CD VARCHAR2(3) NOT NULL ,
SRC_CD_DATE DATE NOT NULL
)
PARTITION BY RANGE (SRC_CD_DATE)
INTERVAL (NUMTODSINTERVAL(1,'day'))
(PARTITION p_first VALUES LESS THAN (TO_DATE('25-OCT-2014','DD-MON-YY')));
2.
Ran these inserts on 5th November. sys date is 11/5
INSERT INTO TEST_PARTITION VALUES(1, 'ABC.csv', 'AC', SYSDATE);
INSERT INTO TEST_PARTITION VALUES(1, 'ABC1.csv', 'AC', SYSDATE+1);
INSERT INTO TEST_PARTITION VALUES(1, 'ABC2.csv', 'AC', SYSDATE+2);
INSERT INTO TEST_PARTITION VALUES(1, 'ABC3.csv', 'AC', SYSDATE+3);
3.
EXEC DBMS_STATS.gather_table_stats(USER, 'TEST_PARTITION');
4.
SELECT table_name, partition_name, high_value, num_rows
FROM user_tab_partitions
where table_name = 'TEST_PARTITION'
ORDER BY table_name, partition_name;
My question is oracle is setting one day higher for each partition when I insert data. I am thinking may be it is because of the day start exactly at midnight or something. The output is coming like :
TEST_PARTITION P_FIRST TO_DATE(' 2014-10-25 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') 0
TEST_PARTITION SYS_P74221 TO_DATE(' 2014-11-06 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') 1
TEST_PARTITION SYS_P74222 TO_DATE(' 2014-11-07 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') 1
TEST_PARTITION SYS_P74223 TO_DATE(' 2014-11-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') 1
TEST_PARTITION SYS_P74224 TO_DATE(' 2014-11-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') 1
I see for the first insert the partition created is SYS_P74221 with high value 2014-11-06 but this should have been 2014-11-05 as I inserted the first record with sysdate which is 11/5. This will create problem while I will need to delete partitions while purging if I go by dates.
Please advice.