Oracle – How to Exchange Partition Between Two Tables with Same Partition Name

oracleoracle-11g-r2partitioningperformanceperformance-tuningplsql

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?

Best Answer

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:

exchange_partition_subpart

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

One nonpartitioned table with:

one range, list, or hash partition

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.

Related Solutions

Oracle Exchange partition from non partitioned table

We have done the same operation using the following approach:

Create the destination table with the correct structure nologging
Insert append parallel into it (verifiy direct path load in the explain plan)
Create the indexes nologging
in some case add the FK novalidate
compute the stats
Alter the objects LOGGING
Drop the initial table

Mysql – How to partition a table by timestamp, where data from every i’th day goes to partition i

You can't use FROM_UNIXTIME() because hash partitions must be based on an integer expression. But assuming your timestamp is stored as an integer, you can use DIV to return an integer.

Here's a demo of partitioning in the way you describe:

mysql> create table table_to_partition (
  my_timestamp int unsigned primary key
) partition by hash(my_timestamp DIV (60*60*24)) partitions 3;

mysql> insert into table_to_partition values (unix_timestamp(now()));
mysql> insert into table_to_partition values (unix_timestamp(now()-interval 1 day));
mysql> insert into table_to_partition values (unix_timestamp(now()-interval 2 day));
mysql> insert into table_to_partition values (unix_timestamp(now()-interval 3 day));
mysql> insert into table_to_partition values (unix_timestamp(now()-interval 4 day));
mysql> insert into table_to_partition values (unix_timestamp(now()-interval 5 day));

mysql> select table_name, partition_name, table_rows 
from information_schema.partitions where table_name='table_to_partition';

+--------------------+----------------+------------+
| table_name         | partition_name | table_rows |
+--------------------+----------------+------------+
| table_to_partition | p0             |          2 |
| table_to_partition | p1             |          2 |
| table_to_partition | p2             |          2 |
+--------------------+----------------+------------+

Best Answer

Related Solutions

Oracle Exchange partition from non partitioned table

Mysql – How to partition a table by timestamp, where data from every i’th day goes to partition i

Related Question