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),
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.
Joseph R.P. Maloney, CSP,CDP,CCP
'The answer is 42'
Click Here to Expand Forum to Full Width