table-switch（表开关）.docVIP

table-switch（表开关） This paper mainly introduces how to partition table and table of data exchange, some problems encountered in the exchange and settlement. The table of experimental and data needs 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 FROM, dba_objects o 1; 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 t_addr WHERE 1=0 * FROM; 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 based on the global index, if the partition table has global index plus the statement will not fail, otherwise the exchange will need to rebuild the global index is invalid Exchange table: tmp_addr Partition table: t_addr Column type or size ORA-14097: ALTER TABLE EXCHANGE PARTITION does not match the Reason: the table structure and table exchange swap to be consistent, such as partition table (t_addr ID) is a primary key, then switching table (tmp_addr) of the ID must have a primary key. ALTER TABLE tmp_addr ADD PRIMARY KEY (ID) increased after the success in the implementation of the exchange. Create partition table primary key default to the global index, then the corresponding field exchange table also need to create a primary key; and cannot contain the index exchange (including INDEXES without VALIDATION). Summary: if the partition table contains a primary key, then the corresponding exchange table must have a primary key fields; But when the exchange cannot contai