-
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. www.dbasupport.com
2. www.dbforums.com
3. www.itpub.net
4. www.csdn.net
5. www.umlchina.com
6. www.tek-tips.com
7. www.cnforyou.com
8. fm365.federal.com.cn
9. www.programmersheaven.com
10.http://msdn.microsoft.com/library/default.asp
ligang1000@hotmail.com
-
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 ....
Last edited by Calvin_Qiu; 04-04-2003 at 08:15 AM.
Oracle Certified Master - September, 2003, the Second OCM in China
*** LOOKING for PART TIME JOB***
Data Warehouse & Business Intelligence Expert
MCSE, CCNA, SCJP, SCSA from 1998
-
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.
Oracle Certified Master - September, 2003, the Second OCM in China
*** LOOKING for PART TIME JOB***
Data Warehouse & Business Intelligence Expert
MCSE, CCNA, SCJP, SCSA from 1998
-
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)
1. www.dbasupport.com
2. www.dbforums.com
3. www.itpub.net
4. www.csdn.net
5. www.umlchina.com
6. www.tek-tips.com
7. www.cnforyou.com
8. fm365.federal.com.cn
9. www.programmersheaven.com
10.http://msdn.microsoft.com/library/default.asp
ligang1000@hotmail.com
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
|