DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: I cannot add partition

  1. #1
    Join Date
    Oct 2000
    Posts
    139
    Hi

    I want to add a partition to a table but I receive error

    ORA-14074: partition bound must collate higher than that of the last partition

    The partiton is ranged by date, currently I have 5 partition

    p1, p2, p3, p4 and pn

    p1 ===> values less than 01/05/2001
    p2 ===> values less than 01/06/2001
    p3 ===> values less than 01/07/2001
    p4 ===> values less than 01/08/2001
    pn ===> values less than (maxvalue)


    I want to add partition p0 for values less than 01/01/2001

    in P1 I have these values

    select max(bill_date), min(bill_date) from billing partition (p1);

    max------------min
    30/04/01-----15/04/99

    I do

    alter table billing add partition p0
    values less than (to_Date('01/01/2001', 'dd/mm/yyyy'))
    ERROR at line 1:
    ORA-14074: partition bound must collate higher than that of the last partition

    then I tried (just for testing)

    alter table billing add partition p0
    values less than (to_Date('15/04/1999', 'dd/mm/yyyy'))
    ERROR at line 1:
    ORA-14074: partition bound must collate higher than that of the last partition


    I also want to add a new partition P5 for values less than 01/09/2001 but receiving same error

    alter table billing add partition p5
    values less than (to_Date('01/09/2001', 'dd/mm/yyyy'))
    ERROR at line 1:
    ORA-14074: partition bound must collate higher than that of the last partition


    Am i wrong adding partitions? Should I split partition for the first case and exchange partition for the second case?



    Thank you


  2. #2
    Join Date
    Mar 2000
    Location
    Chennai.Tamilnadu.India.
    Posts
    658

    solution

    Hi, 12th Aug 2001 19:56 hrs chennai

    Yes you have given the solution: split the partition

    Example
    =====

    create table pad(a number,b date) partition by range(b)
    (PARTITION p1 VALUES LESS THAN(TO_DATE('01-MAY-2001','DD-MON-YYYY')),
    PARTITION p2 VALUES LESS THAN(TO_DATE('01-JUN-2001','DD-MON-YYYY')),
    PARTITION p3 VALUES LESS THAN(TO_DATE('01-JUL-2001','DD-MON-YYYY')),
    PARTITION p4 VALUES LESS THAN(TO_DATE('01-AUG-2001','DD-MON-YYYY')),
    PARTITION p5 VALUES LESS THAN(MAXVALUE));

    Your Problem 1 and solution
    ==================
    ALTER TABLE PAD SPLIT PARTITION p1 AT
    (TO_DATE('01-JAN-2001','DD-MON-YYYY'))
    INTO (PARTITION p01 ,PARTITION p1);

    Your Problem 2 and solution
    ==================

    ALTER TABLE PAD SPLIT PARTITION p5 AT
    (TO_DATE('01-SEP-2001','DD-MON-YYYY'))
    INTO (PARTITION p51 ,PARTITION p5);

    In end check this view to confirm your doubt
    =============================

    select PARTITION_NAME,HIGH_VALUE from ALL_TAB_PARTITIONS where TABLE_NAME='PAD';

    You can just copy the above example paste it in SQL PLUS and try it.

    Still you have doubt feel free to get me at this thread.

    Cheers

    Padmam


    Attitude:Attack every problem with enthusiasam ...as if your survival depends upon it

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width