-
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!
-
I would not call it a wierd truncation, but consider that data conversion usually sets the time component to midnight. Therefore, your 11-30-01 becomes 11-30-01 00:00:00. If you have data that was done later that day, say a 2pm, it would have a time of 11-30-01 14:00:00 and not qualify for the partition without an overflow partition there would be no place to put it.
