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
    Why do you want to make it directly? There is no performance problem, it is only a ddl operation, at least the switch to the table. – miracle173 Aug 4 '15 at 19:23
  • I was curious because 1. less code 2. was unaware of performance issue. Thanks Miracle – Sameer Pradhan Aug 4 '15 at 19:29

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.

|improve this answer|||||
  • No, that is not true. If the partition contains subpartitions you have to exchange it with a partitioned table withoutub spartitiones. – miracle173 Aug 4 '15 at 19:49
  • 1
    Who mentioned sub-partitions? The OP makes no mention of that requirement. – Max Vernon Aug 4 '15 at 20:48
  • the sentence "you can only exchange partitions from a partitioned table to a non-partitioned table" is false. – miracle173 Aug 4 '15 at 20:53
  • 3
    Ahhh thanks for clarifying that. Do you have a reference to that somewhere in the documentation or something? – Max Vernon Aug 4 '15 at 20:58
  • 1
    SQL Language Reference tells what can be exchanged. – miracle173 Aug 5 '15 at 4:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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