Oracle Database Discussions

I have an Oracle 10g2 table that is partitioned by range daily on a date field. New partitions are created daily for the next day via a procedure. All is fine and the birds are singing and everybody lives in harmony...tra-la.

To test the procedure after some tweaks I delete the last partition(s) from the table (partitions named AUG_26_2009, AUG_27_2009,AUG_28_2009). My procedure faithfully (tra-la!) produces the alter table statements in the correct sequence and syntax to re-create the partitions:

alter table [table_name] add partition AUG_26_2009 values less than (to_date('27-AUG-09', 'dd-mon-yyyy')) TABLESPACE [tablespace]
alter table [table_name] add partition AUG_27_2009 values less than (to_date('28-AUG-09', 'dd-mon-yyyy')) TABLESPACE [tablespace]
alter table [table_name] add partition AUG_28_2009 values less than (to_date('29-AUG-09', 'dd-mon-yyyy')) TABLESPACE [tablespace]

Only when I uncomment the actual line that creates them, I get the dreaded "ORA-14074: partition bound must collate higher than that of the last partition" error.

OK, the table does NOT have a maxvalue nor a default value partition, so I shouldn't have to SPLIT any partition. The high_value for the table is
TO_DATE(' 2009-08-26 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') which is less than the new partitions I'm trying to create.
I step through the code and verify that, indeed, the procedure is creating the alter table statements in the correct order (above). Now the birds have stopped singing and life s**ks!!

Can someone shed light on this situation?... I mean about the table, not the birds.

Edited by: user571710 on Aug 27, 2009 1:08 PM