Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2002
    Location
    Rio de Janeiro, Brazil
    Posts
    20

    Unanswered: partition maintenence

    hi all,

    i have a table range partitionned and i want to change partition rules and redistribute data among the new rule.

    here's the partition rule:

    PARTITION BY RANGE(CUSTOMER_ID)
    (PARTITION A VALUES LESS THAN (1000000)
    STORAGE(FREELIST GROUPS 1)
    TABLESPACE A,
    PARTITION B VALUES LESS THAN (2000000)
    STORAGE(FREELIST GROUPS 1)
    TABLESPACE B ,
    PARTITION C VALUES LESS THAN (3000000)
    STORAGE(FREELIST GROUPS 1)
    TABLESPACE C,
    PARTITION D VALUES LESS THAN (4000000)
    STORAGE(FREELIST GROUPS 1)
    TABLESPACE D,
    PARTITION E VALUES LESS THAN (5000000)
    STORAGE(FREELIST GROUPS 1)
    TABLESPACE E,
    PARTITION F VALUES LESS THAN (MAXVALUE)
    STORAGE(FREELIST GROUPS 1)
    TABLESPACE F)
    ENABLE ROW MOVEMENT


    CUSTOMER_ID is a sequence number and all data is stored only in the last partition due to initial value of the sequence (6800000).

    whats the best/fastest way to change rule partition and redistribute the data ? partition merge ? partition split ?

    i'm a kind of lost in this situation.

    thanks all.

    Leandro.
    Oracle DBA.

  2. #2
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    Do you have any data in this table?
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  3. #3
    Join Date
    Sep 2002
    Location
    Rio de Janeiro, Brazil
    Posts
    20

    partition maintenence

    Yes,

    I have 17.000.000 rows, all of them at the last partition, and last tablespace.

  4. #4
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    So, I guess You can Drop all the empty partitions ( i.e, the A, B, C, D, E) and Split the Partitions F into 5 more partitions. Please follow the steps below.

    (1) If you have 17000000 Rows in the table and Customer_id starts with 62000, then the last customer_id is 238000. You want 6 partitions (A to F). So On Average, You want about 2830000 Rows in each partitions.

    (2) So split the partitions F into 2 with F having Customer_id LESS THAN 9630000 (2830000 + 6800000). And the second partitions, say, A with the remaining Customer_id.

    (2) Same way, You can Split A into A, B and so on.

    Hope This Helps.
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  5. #5
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    Please NOTE: in the above reply, Please change 62000 to 6800000. (In step 1)

    Thanks
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  6. #6
    Join Date
    Sep 2002
    Location
    Rio de Janeiro, Brazil
    Posts
    20
    Bhavin,


    thanks VERY MUCH !!!


    best regards,


    Leandro
    Oracle DBA.

Posting Permissions

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