Migrating Normal Table to Partition Table
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Migrating Normal Table to Partition Table

Hybrid View

  1. #1
    Join Date
    Aug 2000
    Location
    Shanghai
    Posts
    433

    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

  2. #2
    Join Date
    Oct 2001
    Location
    Madrid, Spain
    Posts
    763
    1) Export the table
    2) Create the table partitioned as you want
    3) Import the table with ignore=yes

    Cheers

  3. #3
    Join Date
    Mar 2002
    Posts
    534
    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

  4. #4
    Join Date
    Apr 2002
    Location
    Shenzhen, China
    Posts
    327
    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

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    search exchange partition

  6. #6
    Join Date
    Mar 2002
    Posts
    534
    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.

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

  8. #8
    Join Date
    Mar 2002
    Posts
    534
    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

  9. #9
    Join Date
    Apr 2002
    Location
    Shenzhen, China
    Posts
    327
    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

  10. #10
    Join Date
    Aug 2000
    Location
    Shanghai
    Posts
    433
    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
  •  


Click Here to Expand Forum to Full Width