解决ORA-14098分区交换索引不匹配错误

上周在客户一套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.askmaclean.com
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功能来对比检验索引:
single_object_compare

对于存在主键的分区表,可以在主键上以DISABLE VALIDATE方式创建unique constraint约束,以代替全局的主键索引。若交换表(Exchange Table)上存在主键索引的话,那么建议在交换前暂时将该索引drop掉,待交换完成后再重建。

如果实在无法解决该ORA-14098错误,那么可以尝试使用EXCLUDING INDEXES子句以跳过索引维护,而在交换完成后重建相关失效索引。

owner = '&OWNER1'

沪ICP备14014813号

沪公网安备 31010802001379号