解决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.oracledatabase12g.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子句以跳过索引维护,而在交换完成后重建相关失效索引。


本文转自maclean_007 51CTO博客,原文链接:http://blog.51cto.com/maclean/1277826


相关文章
|
7月前
|
SQL Oracle 关系型数据库
Oracle 将表中的数据查出更改某一字段的值后再插入该表
Oracle 将表中的数据查出更改某一字段的值后再插入该表
148 2
|
Oracle 关系型数据库
ORA-01779: 无法修改与非键值保存表对应的列
ORA-01779: 无法修改与非键值保存表对应的列
ORA-01779: 无法修改与非键值保存表对应的列
|
SQL Oracle 关系型数据库
[20171019]绑定变量的分配长度7.txt
[20171019]绑定变量的分配长度7.txt --//如果绑定变量中字符串分配占用空间的长度变化,oracle会建立子光标。 --//参考连接: http://blog.
1073 0
|
SQL Oracle 关系型数据库
如何格式化不属于任何段的损坏块 (文档 ID 1526163.1)
如何格式化不属于任何段的损坏块 (文档 ID 1526163.1) 类型: 状态: ...
986 0
|
SQL Oracle 关系型数据库
Oracle 反向键索引的原理和用途(减少索引热点块)
Oracle 反向键索引的原理和用途(减少索引热点块) 我们知道Oracle会自动为表的主键列建立索引,这个默认的索引是普通的B-Tree索引。对于主键值是按顺序(递增或递减)加入的情况,默认的B-Tree索引并不理想。
1543 0
|
Oracle 关系型数据库 索引
Oracle索引或这类索引的分区处于不可用状态 查询
ORA-01502: 索引或这类索引的分区处于不可用状态 原因: 出现这个问题,可能有人move过表,或者disable 过索引。 1. alter table xxxxxx move tablespace xxxxxxx 命令后,索引就会失效。
1971 0