Tricky situation when creating partition by date
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Tricky situation when creating partition by date

  1. #1
    Join Date
    Jul 2002
    Posts
    4
    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!

  2. #2
    Join Date
    Apr 2001
    Location
    Louisville KY
    Posts
    295
    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'

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width