Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2003
    Location
    germany
    Posts
    17

    Unanswered: Changing the values less clause in table-partition

    There is a table with a partitioning like

    TABLESPACE bvbw STORAGE (INITIAL 32M BUFFER_POOL DEFAULT)
    PARTITION BY RANGE (dabf$) (
    PARTITION par$dabf$41 VALUES LESS THAN (TO_DATE ('2006-12-10','YYYY-MM-DD')) TABLESPACE bvbw,
    PARTITION par$dabf$42 VALUES LESS THAN (TO_DATE ('2007-06-10','YYYY-MM-DD')) TABLESPACE bvbw,
    PARTITION par$dabf$00 VALUES LESS THAN (MAXVALUE) TABLESPACE bvbw);
    --
    Now i have to change the less clause of par$dabf$41 to 2006-12-11,
    par$dabf$42 to 2007-06-11. Just adding one day.
    --
    I habe no idea how to do that. dbms_redefinition does not work.
    Any idea how to resolve the problem ? Thanks in advance.

    Chris

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    The best idea is not to change partition bounds at all.

    You may try SPLIT changed partitions to new bounds and then MERGE them for desired result, but the splitting will be very slow if the partition containes data for both partitions.

    Alternatively you may copy (INSERT SELECT) data from changed partitions into correctly partitioned table, drop and create involved partitions and EXCHANGE them (using auxiliary table) with copied data. Not an easy way, maybe a little faster for large data.

    Words in capitals are commands or options used in ALTER TABLE command.

  3. #3
    Join Date
    Mar 2003
    Location
    germany
    Posts
    17
    Thanks a lot for the answer.

    in fact there are 16 tables with an average of 8 partitions to change.
    Therefore it seems to take a long time. I will inform myself if it is worth
    that effort.

    Chris

Posting Permissions

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