Range Partitioning with NULL values
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Range Partitioning with NULL values

Hybrid View

  1. #1
    Join Date
    Nov 2000
    Location
    Birmingham, UK
    Posts
    360

    Range Partitioning with NULL values

    I've got a table I want to range partition (version 9204) and keep 3 months of data, creating a new partition every month and dropping the old one. The problem is that the partition column allows NULL values as it only updates once another program processes the row.
    Setting the MAXVALUE allows these NULLs to be stored but then I cant use ADD PARTITION to add a new partition every month:
    Can anyone give me some suggestions?

    Code:
    Table Name                     Partition Name                     Num Rows    Size (Mb)
    ------------------------------ ------------------------------ ------------ ------------
    ****************              JAN_2007                          9,378,280        1,794
    ****************              FEB_2007                          8,531,160        1,639
    ****************              MAR_2007                         10,832,340        2,078
    ****************              APR_2007                          8,486,340        1,625
    ****************              MAY_2007                          3,957,960          761
    ****************              MAX_VALUE                           873,380          160
    When trying to add a partition for JUN_2007 I get

    Code:
    ERROR at line 1:
    ORA-14074: partition bound must collate higher than that of the last partition
    ORA-06512: at line 8
    I cant work out how I can use SPLIT PARTITION to get what I want


    Thanks,
    Last edited by fraze; 05-09-2007 at 02:04 AM.

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    Have a look

    http://download-east.oracle.com/docs...ments_3001.htm

    Code:
    ALTER TABLE sales SPLIT PARTITION MAX_VALUE
       AT (TO_DATE('01-JUN-2007','DD-MON-YYYY'))
       INTO (PARTITION JUN_2007, MAX_VALUE);

  3. #3
    Join Date
    Nov 2000
    Location
    Birmingham, UK
    Posts
    360

    Red face

    Doh! Thanks for that - couldnt see the forest for the trees on that one, was trying to split the MAY partition!

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