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

Thread: create partition error

  1. #1
    Join Date
    Apr 2002
    Posts
    291

    create partition error

    Hi gurus,
    I have a table with more than 15 millions records. Its partitioned on range basis. The highiest value it had was 3500, and maxvalue based on the number of client facilities. Now i want to add some more partitions, and i'm getting following error. Please help me, this is on production. I've to do it before users come in. I've to add 6 more partitions to near about 11 tables like this.
    I'm running oracle 9i on linux 7.1

    Many thanks in advance


    SQL> alter table d_XXX
    add partition P_D_CLAIM_3600 VALUES LESS THAN (3700)
    TABLESPACE XXXDATA01;
    add partition P_D_CLAIM_3600 VALUES LESS THAN (3700)
    *
    ERROR at line 2:
    ORA-14074: partition bound must collate higher than that of the last partition

    SQL> select high_value from dba_tab_partitions where table_name='D_XXX' AND TABLE_OWNER='ABC';

    HIGH_VALUE
    --------------------------------------------------------------------------------
    1100
    1200
    1300
    1400
    1500
    1600
    1700
    1800
    1900
    2000
    2100

    HIGH_VALUE
    --------------------------------------------------------------------------------
    2200
    2300
    1000
    2400
    2500
    2600
    2700
    2800
    2900
    3000
    3100

    HIGH_VALUE
    --------------------------------------------------------------------------------
    3200
    3300
    3400
    3500
    MAXVALUE

    27 rows selected.

    Thanks
    PNRDBA

  2. #2
    Join Date
    Feb 2003
    Posts
    85
    how about read some manual boy?

  3. #3
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    well you have this error

    ERROR at line 2:
    ORA-14074: partition bound must collate higher than that of the last partition

    because your original partition was created with somethin like
    was defined as MAXVALUE on the partition key


    somethin like this

    CREATE TABLE monthly_sales (colspec...)
    PARTITION BY RANGE (col1,col2...)
    (PARTITION P_D_CLAIM_300 VALUES LESS THAN (val) TABLESPACE p1,
    PARTITION P_D_CLAIM_350 VALUES LESS THAN (val2) TABLESPACE p2,
    PARTITION P_D_CLAIM_max VALUES LESS THAN (MAXVALUE) TABLESPACE p3);

    now you need to drop that partition

    ALTER TABLE monthly_sales DROP PARTITION P_D_CLAIM_max

    ALTER TABLE monthly_sales ADD PARTITION P_D_CLAIM_3600 VALUES LESS THAN (3700)

    ALTER TABLE monthly_sales ADD PARTITION P_D_CLAIM_max values less than (MAXVALUE);

    be sure to back up the contents of the P_D_CLAIM_max before dropping it..

    Please use only one thread for posting..if you use two or more threads it will be difficult for a person to make searches on the same topic..:-)

    regards
    Hrishy

  4. #4
    Join Date
    Apr 2002
    Posts
    291
    Thanks for your suggestion hrishy, :-), but the point is, i can't drop the partition, b'coz its on production. Actually i know that we have to drop that max_value partition, and create with those from 3700... but i'm wondering is there any other way round to create...

    any way, thanks for the help once again hrishy....

    PNRDBA

  5. #5
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    If you are on oracle8i then yes..you could do a split partition
    somethin like

    ALTER TABLE monthly_sales split partiton P_D_CLAIM_3600 at (3600)
    into (partiton P_D_CLAIM_3600 ,partition P_D_CLAIM_MAX )

    regards
    Hrishy

  6. #6
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Code:
    SQL>create table test (c1 number) partition by range (c1)
      2  ( partition p10 values less than (10),
      3    partition p20 values less than (20),
      4    partition p30 values less than (30),
      5*   partition pmax values less than (maxvalue))
    SQL> /
    
    Table created.
    
    SQL> select high_value from dba_tab_partitions where table_name = 'TEST';
    
    HIGH_VALUE
    --------------------------------------------------------------------------------
    10
    20
    30
    MAXVALUE
    
    SQL> alter table test add partition p40 values less than (40);
    alter table test add partition p40 values less than (40)
                                   *
    ERROR at line 1:
    ORA-14074: partition bound must collate higher than that of the last partition
    
    SQL> alter table test split partition pmax at (40) into (partition p40, partition pmax);
    
    Table altered.
    
    SQL> select high_value from dba_tab_partitions where table_name = 'TEST';
    
    HIGH_VALUE
    --------------------------------------------------------------------------------
    10
    20
    30
    MAXVALUE
    40
    HTH
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  7. #7
    Join Date
    Apr 2002
    Posts
    291
    Thanks a lot sanjay and hrishy. I'm using oracle 9i. It worked out for me.

    PNRDBA

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