上周在客户一套BRM系统上执行分区交换Exchange Partition操作的时候出现了ORA-14098错误，该错误是由于分区表上的LOCAL分区索引与非分区表上的索引不匹配造成的，我们来看一下这个错误:
[oracle@rh2 ~]$ oerr ora 14098
14098, 00000, "index mismatch for tables in ALTER TABLE EXCHANGE PARTITION"
// *Cause: The two tables specified in the EXCHANGE have indexes which are
// not equivalent
// *Action: Ensure that the indexes for the two tables have indexes which
// follow this rule
// For every non partitioned index for the non partitioned table,
// there has to be an identical LOCAL index on the partitioned
// table and vice versa. By identical, the column position, type
// and size have to be the same.
SQL> ALTER TABLE sales EXCHANGE PARTITION SALES_Q4_2003 with
table SALES_TMP INCLUDING INDEXES WITH VALIDATION UPDATE GLOBAL INDEXES;
ALTER TABLE sales EXCHANGE PARTITION SALES_Q4_2003 with
table SALES_TMP INCLUDING INDEXES WITH VALIDATION UPDATE GLOBAL INDEXES
*
ERROR at line 1:
ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION
如果表上有很多的索引，以至于你无法确定到底是哪个索引引发了ORA-14098错误，那么我们可以通过trace的方式来协助定位到具体的索引:
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
www.askmac.cn
SQL> alter session set max_dump_file_size = unlimited;
Session altered.
SQL> alter session set events '10046 trace name context forever, level 12';
Session altered.
SQL> alter session set events '14098 trace name errorstack forever, level 4';
Session altered.
##SQL> alter system flush buffer_cache;
System altered.
Rerun Exchange Partition DDL
SQL> ALTER TABLE sales EXCHANGE PARTITION SALES_Q4_2003 with table SALES_TMP
INCLUDING INDEXES WITH VALIDATION UPDATE GLOBAL INDEXES;
ALTER TABLE sales EXCHANGE PARTITION SALES_Q4_2003 with table SALES_TMP
INCLUDING INDEXES WITH VALIDATION UPDATE GLOBAL INDEXES
*
ERROR at line 1:
ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION
11g中直接查询v$diag_info就可以得到trace的路径，10g执行gettracename.sql
SELECT d.VALUE
|| '/'
|| LOWER (RTRIM (i.INSTANCE, CHR (0)))
|| '_ora_'
|| p.spid
|| '.trc' trace_file_name
FROM (SELECT p.spid
FROM v$mystat m, v$session s, v$process p
WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,
(SELECT t.INSTANCE
FROM v$thread t, v$parameter v
WHERE v.NAME = 'thread'
AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
(SELECT VALUE
FROM v$parameter
WHERE NAME = 'user_dump_dest') d;
TRACE_FILE_NAME
--------------------------------------------------------------------------------
/s01/admin/G10R2/udump/g10r2_ora_17749.trc
==========================10046/errorstack trace contents========================
PARSING IN CURSOR #1 len=127 dep=0 uid=64 oct=15 lid=64 tim=1277655207436065 hv=1207961095 ad='9098f018'
ALTER TABLE sales EXCHANGE PARTITION SALES_Q4_2003 with table SALES_TMP
INCLUDING INDEXES WITH VALIDATION UPDATE GLOBAL INDEXES
END OF STMT
PARSE #1:c=0,e=1145,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1277655207436059
BINDS #1:
STAT #2 id=2 cnt=0 pid=1 pos=1 obj=98001 op='INDEX FULL SCAN SALES_UNID_TMP (cr=1 pr=0 pw=0 time=39 us)'
*** 2011-06-17 21:55:32.417
ksedmp: internal or fatal error
ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION
Current SQL statement for this session:
ALTER TABLE sales EXCHANGE PARTITION SALES_Q4_2003 with table SALES_TMP
INCLUDING INDEXES WITH VALIDATION UPDATE GLOBAL INDEXES
我们可以在trace中看到在出现ORA-14098错误前，正在对索引SALES_UNID_TMP的Fast Full Scan
除了通过10046/errorstack的trace信息诊断外，更多的问题可以直接从DDL语句中发现,在以上示例中非分区表的DDL语句:
-- Create table
create table SALES_TMP
(
UNI_ID NUMBER NOT NULL,
PROD_ID NUMBER not null,
CUST_ID NUMBER not null,
TIME_ID DATE not null,
CHANNEL_ID NUMBER not null,
PROMO_ID NUMBER not null,
QUANTITY_SOLD NUMBER(10,2) not null,
AMOUNT_SOLD NUMBER(10,2) not null
);
create index SALES_CHANNEL_TMP ON SALES_TMP (CHANNEL_ID) ;
create index SALES_CUST_TMP ON SALES_TMP (CUST_ID) ;
create index SALES_UNID_TMP ON SALES_TMP (UNI_ID,TIME_ID); --注意细节该索引是非UNIQUE的
create index SALES_PROD_TMP ON SALES_TMP (PROD_ID) ;
create index SALES_PROMO_TMP ON SALES_TMP (PROMO_ID) ;
create index SALES_TIME_TMP ON SALES_TMP (TIME_ID) ;
下为分区表的DDL语句:
-- Create table
create table SALES
(
UNI_ID NUMBER NOT NULL,
PROD_ID NUMBER not null,
CUST_ID NUMBER not null,
TIME_ID DATE not null,
CHANNEL_ID NUMBER not null,
PROMO_ID NUMBER not null,
QUANTITY_SOLD NUMBER(10,2) not null,
AMOUNT_SOLD NUMBER(10,2) not null
)
partition by range (TIME_ID)
...............
create index SALES_CHANNEL ON SALES (CHANNEL_ID) LOCAL;
create index SALES_CUST ON SALES (CUST_ID) LOCAL;
create UNIQUE index SALES_UNID ON SALES (UNI_ID,TIME_ID) LOCAL; -- 对应的索引是UNIQUE的
create index SALES_PROD ON SALES (PROD_ID) LOCAL;
create index SALES_PROMO ON SALES (PROMO_ID) LOCAL;
create index SALES_TIME ON SALES (TIME_ID) LOCAL;
解决ORA-14098错误的要点是要找出引发错误的原因。当我们交换分区的时候，我们要确保所有交换表上的索引和分区表上的本地索引匹配。这意味着如果在分区表上有N个LOCAL INDEXES，那么在交换表上就应当有N个等价的索引。这里的等价要求存在映射关系的2个索引，在列的位置、类型、大小及UNIQUE/NON-UNIQUE都要一致。
可以利用如下SQL语句来找出分区表和交换表上索引的差异:
set linesize 160 pagesize 1400
col TABLE_NAME for a30
col INDEX_NAME for a30
col COLUMN_NAME for a30
col COLUMN_POSITION for 99
col COLUMN_LENGTH for 99
col CHAR_LENGTH for 99
col DESCEND for a4
Select TABLE_NAME,INDEX_NAME, COLUMN_NAME,COLUMN_POSITION, COLUMN_LENGTH, CHAR_LENGTH, DESCEND
FROM SYS.DBA_IND_COLUMNS DICN
WHERE INDEX_OWNER = '&own'
and DICN.TABLE_NAME in ('&TABNAME1','&TABNAME2')
ORDER BY INDEX_NAME, COLUMN_POSITION
/
select TABLE_NAME, INDEX_NAME, INDEX_TYPE, UNIQUENESS, PARTITIONED
from dba_indexes
where owner='&OWNER'
and TABLE_NAME in ('&TABNAME1', '&TABNAME2')
order by index_name
/
也可以使用Toad的Single Schema Object Compare功能来对比检验索引:
对于存在主键的分区表，可以在主键上以DISABLE VALIDATE方式创建unique constraint约束，以代替全局的主键索引。若交换表(Exchange Table)上存在主键索引的话，那么建议在交换前暂时将该索引drop掉，待交换完成后再重建。
如果实在无法解决该ORA-14098错误，那么可以尝试使用EXCLUDING INDEXES子句以跳过索引维护，而在交换完成后重建相关失效索引。
owner = '&OWNER1'
drop table SALES_TMP;drop table SALES;create table SALES_TMP( UNI_ID NUMBER NOT NULL, PROD_ID NUMBER not null, CUST_ID NUMBER not null, TIME_ID DATE not null, CHANNEL_ID NUMBER not null, PROMO_ID NUMBER not null, QUANTITY_SOLD NUMBER(10,2) not null, AMOUNT_SOLD NUMBER(10,2) not null);create index SALES_CHANNEL_TMP ON SALES_TMP (CHANNEL_ID) ;create table SALES( UNI_ID NUMBER NOT NULL, PROD_ID NUMBER not null, CUST_ID NUMBER not null, TIME_ID DATE not null, CHANNEL_ID NUMBER not null, PROMO_ID NUMBER not null, QUANTITY_SOLD NUMBER(10,2) not null, AMOUNT_SOLD NUMBER(10,2) not null)PARTITION by range (TIME_ID)(PARTITION p1 values less than (to_date(‘2010-01-01′,’YYYY-MM-DD’)) TABLESPACE users, PARTITION p2 values less than (to_date(‘2011-01-01′,’YYYY-MM-DD’)) TABLESPACE users, PARTITION p3 values less than (to_date(‘2012-01-01′,’YYYY-MM-DD’)) TABLESPACE users ); create index SALES_CHANNEL ON SALES (CHANNEL_ID) LOCAL;ALTER TABLE sales EXCHANGE PARTITION p2 with table SALES_TMP WITHOUT VALIDATION;col partition_name for a30 select partition_name,status from dba_ind_partitions where index_name=’SALES_CHANNEL’; SQL> select partition_name,status from dba_ind_partitions where index_name=’SALES_CHANNEL’;PARTITION_NAME STATUS—————————— —————-P1 USABLEP2 UNUSABLEP3 USABLE