help on partitioning
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: help on partitioning

  1. #1
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333

    help on partitioning

    I recently made a booboo and made a table with 16 hash partitions instead of 12, now I want to delete these extra 4 partitions but keep the data.

    I thought I might be able to just select the data in those partitions out, drop the partitions and then insert the data and the has partitioning would sort itself out - but I can't drop a hashed partition.

    So I then thought I would use ctas to create a new table and then drop the old table and rename the new one but I have so many referential constraints, I would have to go disable them temporarily

    Does anyone have a better option or am I going to have to use the drop and disable my constrints method.

    This is all on 9.2.0.4

    Cheers

    Dave

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    You definately have to recreate the table, so you'll have to deal with those pesky constraints etc.

    However, 16 is a good number of hash partitions (ie., it's an integer power of two). Why the preference for 12?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    well we create the partitions in there own tablespaces - and a tablespace per filesystem (or a factor of 2 per filesystem) - so in this case 12 filesystems so 12 partitions.

    we normally do them in powers of 2 - but this customer had to be different (i.e 2 4 8 16 or 32)

    That fact that I got 16 partitions means tablespaces 1-4 are twice as full as the other ones, this isnt a problem in itself but I like to keep everything in sync with each other and have them the same size.

    The sizings for the tablespaces are also fairly exact so dont want 1-4 to blow up early

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Try exchange partitions...
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    For you windont guys...
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

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