Home » SQL & PL/SQL » SQL & PL/SQL » Merging of Interval Partitions (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production)
Merging of Interval Partitions [message #664350] |
Wed, 12 July 2017 08:46 |
|
saipradyumn
Messages: 419 Registered: October 2011 Location: Hyderabad
|
Senior Member |
|
|
Hi All,
when I am merging the two adjacent partitions(based on their positions) into highest partitions of the two existing partitions,We got the following exception in UAT
Exception :
ORA-14274: partitions being merged are not adjacent.
Partition Positions with high value when we had exception :
Pos Name High Value Table_Name
1 SYS_P32868 TO_DATE(' 2017-01-07 ', 'SYYYY-MM-DD ', 'NLS_CALENDAR=GREGORIAN') TABLE_PROBLMATIC
2 SYS_P32880 TO_DATE(' 2017-01-10 ', 'SYYYY-MM-DD ', 'NLS_CALENDAR=GREGORIAN') TABLE_PROBLMATIC
3 SYS_P32892 TO_DATE(' 2017-01-11 ', 'SYYYY-MM-DD ', 'NLS_CALENDAR=GREGORIAN') TABLE_PROBLMATIC
4 SYS_P32904 TO_DATE(' 2017-01-12 ', 'SYYYY-MM-DD ', 'NLS_CALENDAR=GREGORIAN') TABLE_PROBLMATIC
5 SYS_P32916 TO_DATE(' 2017-01-13 ', 'SYYYY-MM-DD ', 'NLS_CALENDAR=GREGORIAN') TABLE_PROBLMATIC
But when I tried with the same scenario in DEV,some times its working fine.
Example:
DROP TABLE INTERVAL_TEST; -- 2017-01-13
CREATE TABLE "QFXMAIN"."INTERVAL_TEST"
(
"TIMESTAMP1" DATE,
"ID" NUMBER
)
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE
(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
)
TABLESPACE "TS_CMASTER" PARTITION BY RANGE
(
"TIMESTAMP1"
)
INTERVAL
(
NUMTODSINTERVAL(1, 'DAY')
)
(
PARTITION "JAN_01_2017" VALUES LESS THAN (TO_DATE('2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
PARTITION "JAN_05_2017" VALUES LESS THAN (TO_DATE('2017-01-05 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
PARTITION "JAN_06_2017" VALUES LESS THAN (TO_DATE('2017-01-06 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
PARTITION "JAN_07_2017" VALUES LESS THAN (TO_DATE('2017-01-07 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
PARTITION "JAN_10_2017" VALUES LESS THAN (TO_DATE('2017-01-10 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
PARTITION "JAN_11_2017" VALUES LESS THAN (TO_DATE('2017-01-11 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
PARTITION "JAN_12_2017" VALUES LESS THAN (TO_DATE('2017-01-12 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
PARTITION "JAN_13_2017" VALUES LESS THAN (TO_DATE('2017-01-13 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
) ;
Results before Merging
Position Name High Value
1 JAN_01_2017 TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
2 JAN_05_2017 TO_DATE(' 2017-01-05 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
3 JAN_06_2017 TO_DATE(' 2017-01-06 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
4 JAN_07_2017 TO_DATE(' 2017-01-07 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
5 JAN_10_2017 TO_DATE(' 2017-01-10 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
6 JAN_11_2017 TO_DATE(' 2017-01-11 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
7 JAN_12_2017 TO_DATE(' 2017-01-12 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
8 JAN_13_2017 TO_DATE(' 2017-01-13 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
Merging
ALTER TABLE INTERVAL_TEST MERGE PARTITIONS JAN_01_2017,JAN_05_2017 INTO PARTITION JAN_05_2017;
ALTER TABLE INTERVAL_TEST MERGE PARTITIONS JAN_05_2017,JAN_06_2017 INTO PARTITION JAN_06_2017;
ALTER TABLE INTERVAL_TEST MERGE PARTITIONS JAN_06_2017,JAN_07_2017 INTO PARTITION JAN_07_2017;
ALTER TABLE INTERVAL_TEST MERGE PARTITIONS JAN_07_2017,JAN_10_2017 INTO PARTITION JAN_10_2017;
Results After Merging :
SELECT Partition_Position , UTP.* FROM SYS.User_Tab_Partitions UTP
WHERE Table_Name ='INTERVAL_TEST' ORDER BY UTP.Partition_Position;
Position Name High Value
1 JAN_10_2017 TO_DATE(' 2017-01-10 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
2 JAN_11_2017 TO_DATE(' 2017-01-11 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
3 JAN_12_2017 TO_DATE(' 2017-01-12 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
4 JAN_13_2017 TO_DATE(' 2017-01-13 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SELECT Dpt.Interval ,DPT.* FROM DBA_PART_TABLES DPT WHERE Table_Name ='INTERVAL_TEST';
INTERVAL PARTITION TYPE
NUMTODSINTERVAL(1, 'DAY') RANGE
Could please help me to understand scenarios when it can be failed & success-ed
Thanks in Advance
SaiPradyumn
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Sep 27 11:09:37 CDT 2024
|