-
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
-
how about read some manual boy?
-
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
-
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
-
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
-
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"
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|