Migrating Normal Table to Partition Table

The Table data grows and we want to make partition of it .

As it's quite huge , What's the best solution to migration it
to the partition table .( the table name must be same )

BTW , oracle version is 8.1.7

1) Export the table
2) Create the table partitioned as you want
3) Import the table with ignore=yes

Cheers

Another way would be to:

1) Create the table partitioned as you want
2) Insert as select from your source table
3) Drop your source table
4) Rename your new table to the desired table name (ALTER TABLE new_table RENAME TO source_table)

HTH
Mike

Direct Path, Parallel Execution, and Nologging Option are available with insert into partitioned_table ... select ....

search exchange partition

Pando,

He hasn't yet any partitionned table and as I undestand he want's to divide in partitions his existing table. So I dont think that exchanging partitions would be the best solution in this case.

1) Create the table partitioned as you want
2) Exchange partition
3) Drop your source table
4) Rename your new table to the desired table name (ALTER TABLE new_table RENAME TO source_table)

http://www.dbasupport.com/forums/sho...ange+partition

Pando,

I understood that you would have to split the partition(s) once you exchanged the partiton. But depending on how many partitons he wants to use it would probably use more time and ressources than simply inserting it once directly in the right partition.

I have to admit that I haven't tested it, if I got some time today I will do it ;)

Mike

SPLIT PARTITION is an expensive operation because it is necessary to scan all rows of the partition being split then insert them one-by-one into the new partitions.

However, if you just wanna keep all original data in a single partition, it would be a good choice to exchange partition.

very nice option -->exchange partition .
but my table is pretty big , I was think maybe it's still better to take export and import ( to save the time)

I suppose that "Insert into ... Select ..." is better.

Quote:

---

Originally posted by mike9
I understood that you would have to split the partition(s) once you exchanged the partiton. But depending on how many partitons he wants to use it would probably use more time and ressources than simply inserting it once directly in the right partition.

I have to admit that I haven't tested it, if I got some time today I will do it ;)

Mike

---

I doubt it. I'd like to see the numbers on that...

"Create table ... nologging as select ..."

... would be the fastest way to build the new table.