I am trying to restore one partition from a backup table. Both tables have partitions with the same name. My goal is to grab the data from the backup table partition x and put it in original table partition x. x is the name of the partition.

I have tried to do it by using an auxiliary table. Is there a way to exchange them directly or any other solution with better performance?


1 Answer 1


Have you seen this? https://community.oracle.com/thread/889338?start=0&tstart=0

It states you can only exchange partitions from a partitioned table to a non-partitioned table, or vice-versa.

You'd need to temporarily exchange the partition into an interim, non-partitioned, table, then into the target partitioned table.

There are some interesting wrinkles to the broadly stated rule above; see the following excerpt taken from the Oracle documentation:


Use the EXCHANGE PARTITION or EXCHANGE SUBPARTITION clause to exchange the data and index segments of:

  • One nonpartitioned table with:

    1. one range, list, or hash partition

    2. one range, list, or hash subpartition

  • One range-partitioned table with the range subpartitions of a range-range or list-range composite-partitioned table partition

  • One hash-partitioned table with the hash subpartitions of a range-hash or list-hash composite-partitioned table partition

  • One list-partitioned table with the list subpartitions of a range-list or hash-list composite-partitioned table partition

In all cases, the structure of the table and the partition or subpartition being exchanged, including their partitioning keys, must be identical. In the case of list partitions and subpartitions, the corresponding value lists must also match.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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