-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|