标签归档：ORA-01502

move lob导致index失效

发表于 2017 年 11 月 22 日 由 惜分飞

在一次数据库升级过程中,因为blug需要move lob，导致表相关index失效,这里通过实验重现
数据库版本

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

创建用户

SQL> create user xff identified by oracle;

User created.

SQL> grant dba to xff;

Grant succeeded.

创建测试表和插入数据

SQL> conn xff/oracle
Connected.

SQL> create table t_xff (id int primary key not null,name varchar2(20),c_lob clob);

Table created.

SQL> insert into t_xff values(1,'www.xifenfei.com','//www.xifenfei.com');

1 row created.

SQL>  insert into t_xff values(2,'www.xifenfei.com','//www.xifenfei.com');

1 row created.

SQL> commit;

Commit complete.

SQL> select index_name,status from user_indexes;

INDEX_NAME                     STATUS
------------------------------ --------
SYS_IL0000090094C00003$$       VALID
SYS_C0011148                   VALID

move lob

SQL> ALTER TABLE t_xff MOVE LOB (c_lob) store as (tablespace users);

Table altered.

SQL>  select index_name,status from user_indexes;

INDEX_NAME                     STATUS
------------------------------ --------
SYS_IL0000090094C00003$$       VALID
SYS_C0011148                   UNUSABLE

SQL>  insert into t_xff values(3,'www.xifenfei.com','//www.xifenfei.com');
 insert into t_xff values(3,'www.xifenfei.com','//www.xifenfei.com')
*
ERROR at line 1:
ORA-01502: index 'XFF.SYS_C0011148' or partition of such index is in unusable
state

这里很明显，当我们move lob之后，表的index变为无效,插入操作无法进行

rebuind index

SQL> alter index xff.SYS_C0011148 rebuild;

Index altered.

SQL> select index_name,status from user_indexes;

INDEX_NAME                     STATUS
------------------------------ --------
SYS_IL0000090094C00003$$       VALID
SYS_C0011148                   VALID

SQL> insert into t_xff values(3,'www.xifenfei.com','//www.xifenfei.com');

1 row created.

SQL> commit;

Commit complete.

这个测试就是告诫自己,做oracle 不要想当然，move lob之后，表相关的index 都会失效，需要rebuild。具体参见:
Bug 6525073 : STBH: INDEX IS IN UNUSABLE STATE AFTER A LOB COLUMN IS MOVED
ALTER TABLE MOVE LOB makes indexes unusable on the parent table (Doc ID 1228324.1)

发表在 Oracle | 标签为 , , | 评论关闭

ORA-1502问题分析解决

发表于 2011 年 12 月 2 日 由 惜分飞

1、出现ORA-1502
接到开发报告，我们报表程序出现SQLCODE=[-1502]错误提示，sql执行不成功，根据这个提示，我猜想是ORA-1502错误，查询ora-1502错误

[oracle@node1 ~]$ oerr ora 1502
01502, 00000, "index '%s.%s' or partition of such index is in unusable state"
// MERGE: 1489 RENUMBERED TO 1502
// *Cause: An attempt has been made to access an index or index partition
//         that has been marked unusable by a direct load or by a DDL
//         operation
// *Action: DROP the specified index, or REBUILD the specified index, or
//         REBUILD the unusable index partition

根据这个提示，错误的原因是因为有index编程unusable state

--查询分区index是否有UNUSABLE的index
SQL> col index_owner for a20
SQL> col index_name for a30
SQL> col partition_name for a20    
SQL>  SELECT INDEX_OWNER, INDEX_NAME, PARTITION_NAME
  2     FROM DBA_IND_PARTITIONS
  3    WHERE INDEX_OWNER NOT IN ('SYS', 'SYSTEM', 'PUBLIC')
  4     AND STATUS = 'UNUSABLE';

INDEX_OWNER          INDEX_NAME                     PARTITION_NAME
-------------------- ------------------------------ --------------------
STAT_YDZJ            IND_STAT_DAY_COMPANY_DAY        P_201111
STAT_YDZJ            IND_STAT_DAY_COMPANY_MOBILE     P_201111
STAT_YDZJ            IND_STAT_DAY_COMPANY_AREA       P_201111

--查询无效全局index和普通index
SQL>  SELECT OWNER, a.index_name
  2     FROM Dba_Indexes  a
  3    WHERE OWNER NOT IN ('SYS', 'SYSTEM', 'PUBLIC')
  4     AND ROWNUM<2;

OWNER                          INDEX_NAME
------------------------------ ------------------------------
STAT_YDZJ                      PK_ND_STAT_DAY_COMPANY

2、解决问题
批量生产sql语句，重建这些unusable index

SELECT 'ALTER INDEX  ' || INDEX_OWNER || '.' || INDEX_NAME ||
       ' REBUILD PARTITION ' || PARTITION_NAME || ' NOLOGGING online;'
  FROM DBA_IND_PARTITIONS
 WHERE INDEX_OWNER NOT IN ('SYS', 'SYSTEM', 'PUBLIC')
   AND STATUS = 'UNUSABLE'
UNION ALL
SELECT 'alter index  ' ||OWNER || '.' || A.INDEX_NAME || ' REBUILD online nologging;'
  FROM DBA_INDEXES A
 WHERE OWNER NOT IN ('SYS', 'SYSTEM', 'PUBLIC')
 AND  STATUS = 'UNUSABLE';

执行完上面sql生成语句后，让开发测试程序，反馈工作正常

3、问题分析
我昨天对STAT_DAY_COMPANY表添加了分区(SPLIT拆分MAXVALUE)，本以为该表的所有index都是local index不知道为何有了一个全局index(公司规定所有分区表都只能建立local index)，所以没有做相关查询，导致这次出现了index无效。但是为什么还有三个local index也变成了unusable，这个估计是大批量append插入数据导致。而开发那边正好是批量append插入数据到STAT_DAY_COMPANY表中，导致出现了ORA-1502错误
建议：处理分区表时，要对index查询清楚，不要按照规定或者惯性思维办事。

发表在 ORA-xxxxx | 标签为 | 评论关闭