-
I want to partition an existing non-partitioned table. Can I do this without recreating the table?
-
Kinda yes, kinda no.
You have to create another partitioned table with one partition and then exchange your existing table into the partitioned table. When the exchange is done, you can slice it up any way you want.
Jeff Hunter
-
Yes it should be possible with range partitioning, but it will create new segements for the new partitions and move the related rows in there which is more or less the same as recreating a new partitioned table and then insert there all data.
To do this you would have to:
1) create a new and empty partitioned table (called "new_part_table") with only one partition (called "part1") with the upper limit VALUES LESS THAN (MAXVALUE)
2) exchange your partiton with you actual table
ALTER TABLE new_part_table
EXCHANGE PARTITION part1 WITH TABLE your_old_table;
3) split your partition
ALTER TABLE new_part_table
SPLIT PARTITION part1 AT ( 500 )
INTO ( PARTITION part1, PARTITION part2);
4) repeat step 3 for additional new partitions
5) you will probably have to rebuild the indexes
I have not tested it but it should work, however I guess that it would be faster to simply create a new partionned table, insert all data, drop your old table and rename your new table to the old table name.
HTH
Mike
-
Thank you all!
Mike's method works. I think it's a usefull alternative of the good old export, re-create table, import.
CfiDBA
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|