create partition error

[pnrdba]

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

[guru_heaven]

how about read some manual boy?

[hrishy]

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

[pnrdba]

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....

[hrishy]

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

[SANJAY_G]

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

[pnrdba]

Thanks a lot sanjay and hrishy. I'm using oracle 9i. It worked out for me.