Alter nonparttioned table into partitioned one
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Alter nonparttioned table into partitioned one

  1. #1
    Join Date
    Mar 2006
    Posts
    2

    Alter nonparttioned table into partitioned one

    Hi,
    I am looking for the best way, to alter an existing large table to a partitioned (probably hash) one. Create a new partioned one, copy all the data, relink foreign keys, drop the old table and rename the new sounds a bit awful. Is there a better way?

    Thaks

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    1. create your partitioned table empty with only one partition as new_table.
    2. exchange table target_table with new_table
    3. rename target_table to table_old
    4. rename new_table to target_table
    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."

  3. #3
    Join Date
    Mar 2006
    Posts
    2
    1. create your partitioned table empty with only one partition as new_table.

    OK

    2. exchange table target_table with new_table
    What do you mean with exchange? Does this also adapt the foreign keys on the target_table to new_table?

    3. rename target_table to table_old
    OK

    4. rename new_table to target_table
    OK

    Thanks

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Quote Originally Posted by kholger
    2. exchange table target_table with new_table
    What do you mean with exchange? Does this also adapt the foreign keys on the target_table to new_table?
    http://download-west.oracle.com/docs...titi.htm#11985

    and I forgot:
    5. split your one partition table to the partitions you really want.
    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
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Quote Originally Posted by marist89
    http://download-west.oracle.com/docs...titi.htm#11985

    and I forgot:
    5. split your one partition table to the partitions you really want.
    Can't split, drop or merge hash partitions, though. Actualy it would be pretty ineffcient to keep splitting, and probably better to create the new table with the required partitions and insert into it.

    kholger, when you say "probably hash", it makes me wonder whether you have got the right reasons worked out for using partitioning ... what problem are you trying to solve?
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

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