We have a table with over 16 mil rows. I am trying to partition it into 9 partitions. Had no problem creating patition table structure and dummy tables. When I try to switch, I got the following error for some of the patition switches:

alter table sales_partition exchange partition part04_10012001_to_11302001 with table dummy_4;

ERROR at line 1:
ORA-14099: all rows in table do not qualify for specified partition

My definition for part03 and part04 and dummy_4 were:

partition part03_01012001_to_09302001 values less than (TO_DATE('30-SEP-2001','DD-MON-YYYY')) tablespace ts_amzn_data storage (initial 1m next 1m pctincrease 0),

partition part04_10012001_to_11302001 values less than (TO_DATE('30-NOV-2001','DD-MON-YYYY')) tablespace ts_amzn_data storage (initial 1m next 1m pctincrease 0),

and

create table dummy_4 as select * from sales where trunc(dte_created) >= '29-SEP-2001' and trunc(dte_created) < '29-NOV-2001' ;

My guess is that since our records are with date values to the seconds, somehow when it was really close to the next day, a record would end up being kept out of the partition because of some weird trunc opeartions.

Could you give me some suggestions as to how to fix this problem? Thanks!