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 Go to next message
saipradyumn
Messages: 311
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


Re: Merging of Interval Partitions [message #664352 is a reply to message #664350] Wed, 12 July 2017 09:09 Go to previous messageGo to next message
Deep Chakraborty
Messages: 10
Registered: June 2006
Junior Member
Are you trying to merge the last partition, if so the last partition in the range section cannot be merged with itself and previous one.
Re: Merging of Interval Partitions [message #664356 is a reply to message #664352] Wed, 12 July 2017 09:23 Go to previous messageGo to next message
saipradyumn
Messages: 311
Registered: October 2011
Location: Hyderabad
Senior Member

Thanks for your help

No I am string from the First position. This one is merged into immediate next position.
Re: Merging of Interval Partitions [message #664360 is a reply to message #664356] Wed, 12 July 2017 09:41 Go to previous message
Deep Chakraborty
Messages: 10
Registered: June 2006
Junior Member
Doc ID 1526571.1 - please check in MOS
Previous Topic: uploading excel with transposed data
Next Topic: Count Distinct Not Working in Case Select Oracle SQL
Goto Forum:
  


Current Time: Sun Dec 17 19:59:04 CST 2017

Total time taken to generate the page: 0.00602 seconds