记录一次ORA-01502主键状态异常问题

一、问题描述

今天处理一次奇怪的主键冲突问题，下面就是整个处理过程分享出来。业务系统的日志表SYS_SCT_USERLOG会记录所有前台操作内容。下午13点半以后系统无法使用报ORA-01502错误。

二、检查建表DDL

根据业务系统前台报错的描述，先检查SYS_SCT_USERLOG建表的DDL语句

[oracle@cmskcdb ~]$ sqlplus cmskc/xxxxx

SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 18 13:59:20 2025

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set line 200
set pagesize 0
set long 99999
set feedback off
set echo offSQL> SQL> SQL> SQL>
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','SYS_SCT_USERLOG','CMSKC') FROM DUAL;

  CREATE TABLE "CMSKC"."SYS_SCT_USERLOG"
   (    "ID" NUMBER NOT NULL ENABLE,
        "COMPID" NUMBER NOT NULL ENABLE,
        "OWNERID" NUMBER,
        "DEPTID" NUMBER,
        "EMPID" NUMBER,
        "EMPCODE" VARCHAR2(50),
        "EMPNAME" VARCHAR2(50),
        "HOSTNAME" VARCHAR2(100),
        "HOSTIP" VARCHAR2(100),
        "MENUNAME" VARCHAR2(100),
        "WINDOW" VARCHAR2(100),
        "WINDOWTITLE" VARCHAR2(100),
        "LOGTYPE" VARCHAR2(20),
        "BUTTONNAME" VARCHAR2(50),
        "EVENTNAME" VARCHAR2(50),
        "CREATEDATE" DATE NOT NULL ENABLE,
        "DWNAME" VARCHAR2(100),
        "COLNAME" VARCHAR2(100),
        "COLDESC" VARCHAR2(100),
        "OLDVALUE" VARCHAR2(800),
        "NEWVALUE" VARCHAR2(1000),
        "PK1NAME" VARCHAR2(100),
        "PK1VALUE" VARCHAR2(100),
        "PK2NAME" VARCHAR2(100),
        "PK2VALUE" VARCHAR2(100),
        "PK3NAME" VARCHAR2(100),
        "PK3VALUE" VARCHAR2(100),
        "PK4NAME" VARCHAR2(100),
        "PK4VALUE" VARCHAR2(100),
        "PK5NAME" VARCHAR2(100),
        "PK5VALUE" VARCHAR2(200),
        "PK6NAME" VARCHAR2(100),
        "PK6VALUE" VARCHAR2(100),
        "PK7NAME" VARCHAR2(100),
        "PK7VALUE" VARCHAR2(100),
        "PK8NAME" VARCHAR2(100),
        "PK8VALUE" VARCHAR2(100),
        "SPLOG1" VARCHAR2(200),
        "SPLOG2" VARCHAR2(200),
         CONSTRAINT "PK_SYS_SCT_USERLOG" PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 10485760 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
  ALTER INDEX "CMSKC"."PK_SYS_SCT_USERLOG"  UNUSABLE ENABLE <----发现异常状态
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 117440512 NEXT 8192 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"

SQL> 

三、查看主键状态

发现DDL中PK_SYS_SCT_USERLOG主键状态有问题UNUSABLE。通过user_indexes确认状态。

SQL> SELECT index_name,index_type,status,tablespace_name FROM user_indexes WHERE table_name = 'SYS_SCT_USERLOG' AND uniqueness = 'UNIQUE';

INDEX_NAME                     INDEX_TYPE                  STATUS   TABLESPACE_NAME
------------------------------ --------------------------- -------- ------------------------------
PK_SYS_SCT_USERLOG             NORMAL                      UNUSABLE    USERS

SQL>

四、查找重复的异常记录

将主键冲突的异常记录找到。发现的确有一条重复ID。相同的主键ID，不同的ROWID。

SQL> SELECT id,count(1) FROM sys_sct_userlog GROUP BY id HAVING count(1)>1;

        ID   COUNT(1)
---------- ----------
   5999045          2

SQL> SELECT a.id,length(a.id),a.rowid FROM sys_sct_userlog a WHERE a.id=5999045;

        ID LENGTH(A.ID) ROWID
---------- ------------ ------------------
   5999045            7 AAAVEWAAHAAF/4IAAO
   5999045            7 AAAVEWAANAALdYIAAA

SQL>

五、处理异常

我们可以通过ALTER SESSION SET SKIP_UNUSABLE_INDEXES=TRUE;或ALTER SYSTEM SET SKIP_UNUSABLE_INDEXES=TRUE;暂时跳过不可用索引的方式。但为了彻底解决问题。我们还是要把异常记录处理了，彻底解决唯一索引的问题。

5.1 备份重复的异常记录

SQL> CREATE TABLE sys_sct_userlog_bak AS SELECT * FROM sys_sct_userlog WHERE id=5999045;

Table created.

SQL>

5.2 删除主键和异常记录

删除重复记录。同时把异常状态主键也删除

SQL> DELETE FROM sys_sct_userlog WHERE id=5999045;
DELETE FROM sys_sct_userlog WHERE id=5999045
*
ERROR at line 1:
ORA-01502: index 'CMSKC.PK_SYS_SCT_USERLOG' or partition of such index is in unusable state


SQL> 

--报错ORA-01502，先删除唯一索引和主键
SQL> ALTER TABLE sys_sct_userlog DROP CONSTRAINT PK_SYS_SCT_USERLOG CASCADE;

Table altered.

SQL> DELETE FROM sys_sct_userlog WHERE id=5999045;

2 rows deleted.

SQL> COMMIT;

Commit complete.

SQL>

5.3 重新添加主键

重新添加原表ID主键，并确认主键状态。

SQL> SELECT id,count(1) FROM sys_sct_userlog GROUP BY id HAVING count(1)>1;

no rows selected

SQL> ALTER TABLE SYS_SCT_USERLOG ADD CONSTRAINT PK_SYS_SCT_USERLOG PRIMARY KEY (id);

Table altered.

SQL> SELECT index_name,index_type,status,tablespace_name FROM user_indexes WHERE table_name = 'SYS_SCT_USERLOG' AND uniqueness = 'UNIQUE';

INDEX_NAME                     INDEX_TYPE                  STATUS   TABLESPACE_NAME
------------------------------ --------------------------- -------- ------------------------------
PK_SYS_SCT_USERLOG             NORMAL                      VALID    USERS

SQL>

5.4 异常记录处理

将重复记录过滤成一条，插回原表

SQL> INSERT INTO sys_sct_userlog SELECT * FROM sys_sct_userlog_bak WHERE rownum=1;

1 row created.

SQL> COMMIT;

Commit complete.

SQL>

六、总结

至此不可用唯一主键索引问题解决，系统正常运行。唯一疑问的是在有唯一主键约束的情况下，为什么还能有重复的异常记录？哪位小伙伴碰到过也请留言告知一下谢谢。