Partition exchange between partitioned tables and common tables

This article describes how to exchange data between a partitioned table and a general table, and several problems encountered during the exchange and solutions.

Tables and data required for the experiment
Create Table t_addr
(
ID number (18) primary key,
Name varchar2 (300 ),
Tag Number
)
Partition by list (TAG)
(
Partition p_area1 values (1 ),
Partition p_area2 values (2 ),
Partition p_area3 values (3 ),
Partition p_area_default values (default ));

Insert into t_addr select O. object_id, O. object_name, 1 from dba_objects O;
Alter table t_addr enable row movement;
Update t_addr set tag = 1;
Update t_addr set tag = 2 where rownum <= 50000;
Update t_addr set tag = 3 where rownum <= 40000;
Update t_addr set tag = 5 where rownum <= 10000;
Begin dbms_stats.gather_table_stats ('kgis ', 't_ ADDR'); end;

Create Table tmp_addr as select * From t_addr where 1 = 0;

Exchange statement:
Alter table t_addr
Exchange partition p_area2
With table tmp_addr
Including Indexes
Without validation
Update global indexes;
Update global indexes -- this statement is for global indexes. If a partition table has a global index, this statement will not expire. Otherwise, the global index will be invalid after the switch and need to be rebuilt.

Exchange table: tmp_addr
Partition Table: t_addr

ORA-14097: the column type or size in alter table exchange partition does not match
Cause: the structure of the exchange table must be the same as that of the table to be partitioned. If the t_addr ID is the primary key, the ID of the exchange table (tmp_addr) must also have a primary key.
Alter table tmp_addr add primary key (ID) is added and the exchange is successful.
The primary key created in the partition table is a global index by default. A primary key is also required for fields in the exchange table. The primary key cannot contain the Index exchange.
(Including indexes without validation ).

Summary: If a partition table contains a primary key, the fields in the exchange table must also have a primary key;
And the exchange does not contain the index, otherwise the ORA-14098 error will be reported, that is, only data can be exchanged.

ORA-14098: Table index mismatch in alter table exchange Partition
Cause:
1. If a local index is created for a non-partition key of the partition table, the field in the exchange table must also be indexed. Otherwise, the above error is reported, as shown in the following example:
Create index idx_addrid on t_addr (ID) local;
Create index idx_tmpid on tmp_addr (ID );
Alter table t_addr
Exchange partition p_area2
With table tmp_addr
Including Indexes
Without validation
Exchange successful
Drop index idx_tmpid;
Alter table t_addr
Exchange partition p_area2
With table tmp_addr
Including Indexes
Without validation
The above error is reported when you delete the Index exchange of the exchange table.

2. If a global index is created for a non-partition key in the partition table, this field in the exchange table cannot be used to create an index. Otherwise, the above error is reported, as shown in the following example:
Drop index idx_addrid;
Create index idx_addrid on t_addr (ID );
Create index idx_tmpid on tmp_addr (ID );
Alter table t_addr
Exchange partition p_area2
With table tmp_addr
Including Indexes
Without validation
The above error is reported during the exchange.

Drop index idx_tmpid
Alter table t_addr
Exchange partition p_area2
With table tmp_addr
Including Indexes
Without validation
After the index is deleted, the exchange is successful.

Note: during the tests of 1 and 2, the partition key tag creates a local index in the partition table, and the exchange table also creates an index, that is, the following statement is created:
Create index idx_addrtag_local on t_addr (TAG) Local
Create index idx_tmptag _ on tmp_addr (TAG)

3. If the partition key of the Partition Table creates a local index, this field of the exchange table must also create an index.
Delete the index of the ID field:
Drop index idx_addrid; -- delete the global index of the ID field of the partition table. Note that the index of this field in the exchange table has also been deleted.
Now, the index is: the partition of the partition table is a local index, and the field corresponding to the exchange table also creates an index. There is no problem in performing the exchange.
If you delete the index of this field in the exchange table, the following error is returned:
Drop index idx_tmptag _;
Alter table t_addr
Exchange partition p_area2
With table tmp_addr
Including Indexes
Without validation

4. If the partition key of the Partition Table creates a global index, this field of the exchange table cannot create an index. Otherwise, the above error is reported.
Drop index idx_addrtag_local;
Create index idx_addrtag_local on t_addr (TAG); -- create a global index
-- The field in the exchange table is not indexed. The exchange is successful.
Alter table t_addr
Exchange partition p_area2
With table tmp_addr
Including Indexes
Without validation

Create index idx_tmptag _ on tmp_addr (TAG );
-- In the exchange table, this field also creates an index and performs the exchange. The above error is reported.
Alter table t_addr
Exchange partition p_area2
With table tmp_addr
Including Indexes
Without validation
Summary: if an index is included in the exchange, whether it is a partition key or a non-partition key, if the Partition Table creates a partition index, the corresponding fields of the exchange table must also be indexed;
If a partition table creates a global index, indexes cannot be created for the fields in the exchange table; otherwise, a ORA-14098 error is reported.

ORA-02266: Unique/primary key-enabled external keyword reference in the table
Cause: the primary key in the Partition Table is referenced by another table. Therefore, before performing the exchange, you must disable the primary key and the foreign key that references the primary key.
The primary key in the exchange table must also be disable and consistent with the partition table.
-- The partition table is the primary key of the exchange table and the foreign key disable that references the primary key.
Alter table partiton_tablename disable primary key cascade; -- When cascade is added, the foreign key that references the primary key is also disable
Alter table change_tablename disable primary key cascade;
Note: Since the primary key is disable, after the switch, remember to enable the primary key and the foreign key that references the primary key.