Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have table name Summary with 86 GB data. This table do not have partition/sub-partition .We want to move the data from the summary table to a composite partitioned table (range partition on entry_date column and hash partition on users_codes column ) .

oracle doesn't allow to exchange data with non partitioned table

So we are doing the following approach.kindly let me know if this is correct approach.

  1. Create a temporary table summary_temp with range partition on entry_date column ,then exchange the data from summary table to partitioned table(summary_temp) in part_hist.
  2. Alter the summary_temp table to add sub partition (hash partition on users_codes) column
  3. exchange the partition and sub_partition data to the another empty destination table which is having the same partition and sub partition.
share|improve this question

migrated from stackoverflow.com Jul 31 '13 at 13:59

This question came from our site for professional and enthusiast programmers.

1 Answer 1

We have done the same operation using the following approach:

  1. Create the destination table with the correct structure nologging
  2. Insert append parallel into it (verifiy direct path load in the explain plan)
  3. Create the indexes nologging
  4. in some case add the FK novalidate
  5. compute the stats
  6. Alter the objects LOGGING
  7. Drop the initial table
share|improve this answer
    
Good list -- could combine 1 & 2, or could consider creating the indexes prior to the insert if they are few in number and the total size of indexed column data is small relative to total size of table data. –  David Aldridge Jul 31 '13 at 11:42
    
Yes, it could be combined using a CTAS but you will loose the exact defintion of columns (NUMBER(8)=>NUMBER) –  Nicolas Durand Jul 31 '13 at 15:12

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.