Oracle exchange partition

Oracle exchange partition Exchange partition provides a way for you to migrate data between a table and a table, or between a partition and a partition. Note that it is not in the form of converting a table into a partition or non-partition, instead, it only migrates data in the table (mutual migration). Because it is claimed to be using the method of changing the data dictionary, the efficiency is the highest (almost no I/O operations involved ). Exchange partition applies to all partition formats. You can migrate data from a partition table to a non-partition table, or migrate data from a non-partition table to a partition table. However, Exchange between range partition and range partition is not supported. Example: SQL> CREATE TABLE ou_a (a INTEGER) 2 PARTITION BY RANGE (a) 3 (PARTITION p1 VALUES LESS THAN (5), 4 PARTITION p2 VALUES LESS THAN (6 ), 5 PARTITION p3 values less than (7), 6 PARTITION p4 values less than (8), 7 PARTITION p5 values less than (9) 8 ); table createdSQL> insert into ou_a values (5); 1 row insertedSQL> insert into ou_a values (6); 1 row insertedSQL> insert into ou_a values (7); 1 row I NsertedSQL> insert into ou_a values (8); 1 row insertedSQL> commit; Commit completeSQL> create index index_ou on ou_a (a) local; Index createdSQL> create table ou_temp (a integer ); table createdSQL> insert into ou_temp values (8); 1 row insertedSQL> commit; Commit completeSQL> alter table ou_a exchange partition p2 with table ou_temp; alter table ou_a exchange partition p2 with table ou_temp ORA-1409 9: No rows in the specified partition limit table this indicates that when a partitioned table is switched, the temporary table's Data Partition key value exceeds the partition's critical value when a ORA-14099 error is reported, if you need to block this error, without validation is required, as follows: SQL> alter table ou_a exchange partition p2 with table ou_temp without validation; Table altered SQL> select. partition_Name,. status from User_Ind_Partitions a where. index_Name = 'index _ OU '; PARTITION_NAME STATUS -------------------------------- -------- P1 USABLEP2 UNUSABLEP3 USABLEP4 USABLE now view the INDEX The local index becomes unavailable in partition p2 after the partition is switched, which indicates that the index may be invalid during exchange partition. SQL> select * from ou_a where a = 8; A ------------------ 8 SQL> select * from ou_a; A ---------------------- 8 6 7 8 when the index fails, to view the entire table, we can find two data records of 8, but when we use where a = 8, we can only find one data record. This is because when where a = 8, oracle uses partition pruning to find partition p5, and because the two values of 8 are stored in p2 and in p5, therefore, using without validation may cause a lot of invalid data and index failure. So how to ensure that the index does not expire? oracle provides the including indexes parameter to ensure that the index is effective after the partition is switched. SQL> CREATE TABLE ou_a (a INTEGER) 2 PARTITION BY RANGE (a) 3 (PARTITION p1 VALUES LESS THAN (5), 4 PARTITION p2 VALUES LESS THAN (6 ), 5 PARTITION p3 values less than (7), 6 PARTITION p4 values less than (8), 7 PARTITION p5 values less than (9) 8 ); table createdSQL> insert into ou_a values (5); 1 row insertedSQL> insert into ou_a values (6); 1 row insertedSQL> insert into ou_a values (7); 1 row in SertedSQL> insert into ou_a values (8); 1 row insertedSQL> commit; Commit completeSQL> create index index_ou on ou_a (a) local; Index createdSQL> create table ou_temp (a integer ); table createdSQL> create index index_temp on ou_temp (a); Index createdSQL> insert into ou_temp values (8); 1 row insertedSQL> commit; commit completeSQL> alter table ou_a exchange partition p2 with table ou_temp including I Ndexes without validation; Table altered SQL> select status from User_Ind_Partitions a where. index_Name = 'index _ OU '; The STATUS--------USABLEUSABLEUSABLEUSABLEUSABLE now finds that the INDEX is exchanged, which means that including indexes can exchange the INDEX.