ORA-01502 – Database SOS

在一次数据库升级过程中,因为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','http://www.xifenfei.com');
1 row created.
SQL>  insert into t_xff values(2,'www.xifenfei.com','http://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','http://www.xifenfei.com');
 insert into t_xff values(3,'www.xifenfei.com','http://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','http://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@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的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

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';

move lob导致index失效

ORA-1502问题分析解决

标签归档：ORA-01502