1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Merging of interval partitions

Discussion in 'SQL PL/SQL' started by Revathi Thirunagari, Jul 9, 2017.

  1. Revathi Thirunagari

    Revathi Thirunagari Active Member

    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Hyderabad
    Hi All,


    when I am merging the two adjacent partitions(based on their positions) into highest partitions of the two existing partitions,

    I am getting the following exception .


    Exception :

    Code (Text):


    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, some times its working fine.

    Example:

    Code (Text):


    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


    Code (Text):


    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

    Code (Text):


        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 :

    Code (Text):


    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

    Revathi Thirunagari
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    748
    Likes Received:
    145
    Trophy Points:
    830
    Location:
    Russian Federation
    Hi.
    What is Oracle version ?

    Provide an example of a command which causes the error...

    p.s. Command of merging not adjacent partitions to generates an error


    For 12c : Merging Multiple Partition
     
    Last edited: Jul 9, 2017
  3. Revathi Thirunagari

    Revathi Thirunagari Active Member

    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Hyderabad
    Thanks for you reply
    Following are the details:

    Oracle Version : 11G

    We are using following merge command

    ALTER TABLE TABLE_PROBLMATIC MERGE PARTITIONS SYS_P32868, SYS_P32880 INTO PARTITION SYS_P32880
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,564
    Likes Received:
    362
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    In the first case the partitions are NOT adjacent since there are 3 days between the high values for two of the partitions. Without enabling row movement you'll get an error. When row movement is enabled then the partitions should not need to be adjacent as the 'merge' will physically move rows to different partitions. This, of course, affects the rowid and will result in indexes needing to be rebuilt.
     
  5. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    748
    Likes Received:
    145
    Trophy Points:
    830
    Location:
    Russian Federation
    Hi,David.
    Where is from the value of 3 (the internal parameter)?
    This in the documentation I not finded.
    The error occurs even if the difference is 2 days .
     
  6. Revathi Thirunagari

    Revathi Thirunagari Active Member

    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Hyderabad

    Hi David

    Thanks for your reply .

    That's why I tried with one more example with the same dates.
    Even that INTERVAL_TEST also contains ROW_MOVEMENT as DISABLED.
    But there i am able to merge the partitions .

    Code (SQL):
    SELECT  Row_Movement , Dt.TABLE_NAME
    FROM SYS.Dba_Tables DT WHERE TABLE_NAME ='INTERVAL_TEST';
    DISABLED    INTERVAL_TEST
     
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,564
    Likes Received:
    362
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Researching this error (which you should also have done) reveals that the two partitions are not 'adjacent', meaning they are not next to each other physically. It works sometimes because the partitions being merged ARE next to each other physically. Since the segments do not 'follow' each other by block address you can't merge them.
     
  8. Revathi Thirunagari

    Revathi Thirunagari Active Member

    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Hyderabad

    Thanks for your help David.

    Now we understood that 'adjacent' means with respect to the physical location of the data block. Its not with respect to the high value or partition position.
    Now my question was how can we make sure that while creating the partitions , it has to create the all those partitions ADJACENT to each other partitions .
    why because all these are INTERVAL Partitions when ever we are inserting the data into the table , Oracle it self internally creating all these partitions.
     
  9. Revathi Thirunagari

    Revathi Thirunagari Active Member

    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Hyderabad

    David
    Could you please help me to understand Partitioning creation .
     
  10. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,564
    Likes Received:
    362
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    A partitioned table, like any other table, is composed of segments which are composed of extents. Segments do NOT need to be contiguous (right next to each other), nor do the extents; they will be created where Oracle has space to do so in a tablespace, and a tablespace can have more than one datafile so segments (and extents) can span the entire set of datafiles for a given tablespace. It's possible (as you have proven) that just because the data range for two extents is 'adjacent' physically they may not be. Your example (that uses the same data ranges) creates adjacent, contiguous partitions; your production table doesn't have that physical layout and the partition merge fails. If you want to see where various segments are in a tablespace you can execute this query:

    select
    ' ' owner, '@#@#@#@#@#@ Free Space @#@#@#@#@#@' object,' ' partition,
    file_id, block_id, blocks,
    (blocks*&dbblksiz)/(1024*1024) meg
    from
    dba_free_space
    where
    tablespace_name=UPPER('&&ts')
    union
    select
    substr(owner,1,20), substr(segment_name, 1,32),partition_name,
    file_id, block_id, blocks,
    (blocks*&dbblksiz)/(1024*1024) meg
    from
    dba_extents
    where
    tablespace_name = upper('&&ts')
    order by 5;

    It might help you understand this problem if you know where segments are in a tablespace.
     
  11. Revathi Thirunagari

    Revathi Thirunagari Active Member

    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Hyderabad
    Thanks for your continuous support David.

    What are the necessary step that we need to take care to make sure this kind of problem will not occur in production once again .I mean when ever we splitting the partitions that has to be in adjacent only
     
  12. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,564
    Likes Received:
    362
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You really can't tell Oracle where to put a partition in a tablespace so such efforts would be wasted. It might be possible to merge the two partitions into a table, then exchange a partition for the table and drop the now-unused partition:

    partition9 and partition10 datam => part9-10 table
    exchange partition9 with part9-10 table
    drop partition10

    This should always work, but you should test it to see that all of the necessary steps have been included.
     
  13. Revathi Thirunagari

    Revathi Thirunagari Active Member

    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Hyderabad
    Thank s David for providing the information.

    Its worked fine with the following alter script also .Before merging the partitions i am applying the below script .
    ALTER TABLE INTERVAL_TEST SET INTERVAL();

    After merging the partitions reverting back to the original position with below script :
    ALTER TABLE INTERVAL_TEST SET INTERVAL((NUMTODSINTERVAL(1,'DAY'));

    If we make the interval to only RANGE, then its allowing me to merge the partitions even though they are not in adjacent.