I have two tables, one partitioned & another non-partitioned table. At the end of each month, data from non-partitioned table is transferred to partitioned table. Two tables are in different schema, current & historical, but both tables has exact same structure. still partition exchange failing with error “ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION”
If I remove “INCLUDING indexes” clause then the partition exchange completes but indexes on both the tables become unusable.
CREATE TABLE CURR.RBT_VDA_PGM
(
SNPSHT_YR_PD_NBR NUMBER NOT NULL,
COL1 VARCHAR2(3 CHAR) NOT NULL,
COL2 VARCHAR2(2 CHAR) NOT NULL,
CRT_TS DATE NOT NULL,
CRT_BY VARCHAR2(250 CHAR) NOT NULL
)
NOLOGGING
PARALLEL (DEGREE 8)
RESULT_CACHE (MODE DEFAULT)
PCTFREE 0
PCTUSED 99
INITRANS 16
STORAGE (INITIAL 100 M NEXT 100 M MAXEXTENTS UNLIMITED)
COMPRESS FOR DIRECT_LOAD OPERATIONS
NOCACHE
MONITORING;
CREATE UNIQUE INDEX CURR.RBT_VDA_PGM_PK ON CURR.RBT_VDA_PGM
(SNPSHT_YR_PD_NBR, COL1, COL2)
NOLOGGING
PCTFREE 1
INITRANS 4
MAXTRANS 255
STORAGE (INITIAL 8 M
NEXT 8 M
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL KEEP
FLASH_CACHE KEEP
CELL_FLASH_CACHE KEEP)
PARALLEL (DEGREE 8 INSTANCES 1);
ALTER TABLE CURR.RBT_VDA_PGM ADD (
CONSTRAINT RBT_VDA_PGM_PK
PRIMARY KEY
(SNPSHT_YR_PD_NBR, COL1, COL2)
USING INDEX CURR.RBT_VDA_PGM_PK
ENABLE VALIDATE);
And historical table as below:
CREATE TABLE HIST.RBT_VDA_PGM
(
SNPSHT_YR_PD_NBR NUMBER NOT NULL,
COL1 VARCHAR2(3 CHAR) NOT NULL,
COL2 VARCHAR2(2 CHAR) NOT NULL,
CRT_TS DATE NOT NULL,
CRT_BY VARCHAR2(250 CHAR) NOT NULL
)
NOLOGGING
PARALLEL (DEGREE 8)
RESULT_CACHE (MODE DEFAULT)
PCTFREE 0
PCTUSED 99
INITRANS 16
STORAGE (INITIAL 100 M NEXT 100 M MAXEXTENTS UNLIMITED)
COMPRESS FOR DIRECT_LOAD OPERATIONS
NOCACHE
MONITORING
PARTITION BY RANGE (SNPSHT_YR_PD_NBR)
INTERVAL (1)
(
PARTITION p202501 VALUES LESS THAN (202502)
);
CREATE UNIQUE INDEX HIST.RBT_VDA_PGM_PK ON HIST.RBT_VDA_PGM
(SNPSHT_YR_PD_NBR, COL1, COL2)
NOLOGGING
PCTFREE 1
INITRANS 4
MAXTRANS 255
STORAGE (INITIAL 8 M
NEXT 8 M
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL KEEP
FLASH_CACHE KEEP
CELL_FLASH_CACHE KEEP)
PARALLEL (DEGREE 8 INSTANCES 1);
ALTER TABLE HIST.RBT_VDA_PGM ADD (
CONSTRAINT RBT_VDA_PGM_PK
PRIMARY KEY
(SNPSHT_YR_PD_NBR, COL1, COL2)
USING INDEX HIST.RBT_VDA_PGM_PK
ENABLE VALIDATE);
Sample values for current table:
INSERT INTO CURR.RBT_VDA_PGM (SNPSHT_YR_PD_NBR, COL1, COL2, CRT_BY, CRT_TS)
VALUES (202501, 'BGT', '28', 'jc', sysdate);
INSERT INTO CURR.RBT_VDA_PGM (SNPSHT_YR_PD_NBR, COL1, COL2, CRT_BY, CRT_TS)
VALUES (202501, 'FRR', '47', 'jc', sysdate);
INSERT INTO CURR.RBT_VDA_PGM (SNPSHT_YR_PD_NBR, COL1, COL2, CRT_BY, CRT_TS)
VALUES (202501, 'TSR', '02', 'jc', sysdate);
COMMIT;
Partion exchange query:
ALTER TABLE SM_HIST.EFM2_RBT_VDA_PGM
EXCHANGE PARTITION FOR (202501)
WITH TABLE SM_APPL.EFM2_RBT_VDA_PGM
INCLUDING indexes
WITHOUT VALIDATION;