Oracle SQL删除旧日志遇ORA-01502，重建索引触发ORA-00604错误求助

针对你遇到的删除数据时触发ORA-01502（主键索引不可用）、重建索引又触发ORA-00604（递归SQL层错误）的问题，可按以下步骤排查解决：

1. 先确认索引与表的状态 #

先执行以下查询，明确问题根源：

• 查看索引状态：

  SELECT INDEX_NAME, STATUS, TABLE_NAME 
  FROM USER_INDEXES 
  WHERE INDEX_NAME = 'PK_IP_LOG_ID';
  

• 检查表的状态：

  SELECT TABLE_NAME, STATUS 
  FROM USER_TABLES 
  WHERE TABLE_NAME = 'IP_LOG_TABLE';
  

• 若为分区表，检查索引分区状态：

  SELECT INDEX_NAME, PARTITION_NAME, STATUS 
  FROM USER_IND_PARTITIONS 
  WHERE INDEX_NAME = 'PK_IP_LOG_ID';
  

2. 排查ORA-00604的触发原因 #

ORA-00604通常和递归SQL执行失败有关，常见诱因包括表空间不足、对象锁定、索引损坏：

• 检查表空间剩余空间：

  SELECT TABLESPACE_NAME, SUM(FREE_SPACE)/1024/1024 AS FREE_SPACE_MB 
  FROM USER_FREE_SPACE 
  GROUP BY TABLESPACE_NAME;
  

• 查看是否有会话锁定索引或表：

  SELECT SID, SERIAL#, STATUS, OSUSER 
  FROM V$SESSION 
  WHERE SID IN (
    SELECT SID FROM V$LOCK 
    WHERE ID1 = (SELECT OBJECT_ID FROM USER_OBJECTS WHERE OBJECT_NAME = 'PK_IP_LOG_ID')
  );
  

• 若存在锁定会话，有权限的话可杀掉：

  ALTER SYSTEM KILL SESSION 'sid,serial#';
  

3. 尝试多种索引重建方式 #

如果直接重建失败，换用以下方式尝试：

• 若为分区索引，尝试分区级重建：

  ALTER INDEX PK_IP_LOG_ID REBUILD PARTITION <partition_name>;
  

• 离线重建索引（避免在线重建的递归逻辑冲突）：

  ALTER INDEX PK_IP_LOG_ID REBUILD OFFLINE;
  

• 指定新表空间重建（若原表空间存在异常）：

  ALTER INDEX PK_IP_LOG_ID REBUILD TABLESPACE <new_tablespace_name>;
  

4. 极端情况：重建主键约束与索引 #

若上述方法均失败，可先禁用主键、删除损坏索引，再重新创建：

• 禁用主键约束：

  ALTER TABLE SCHEME.IP_LOG_TABLE DISABLE CONSTRAINT PK_IP_LOG_ID;
  

• 删除损坏的索引：

  DROP INDEX SCHEME.PK_IP_LOG_ID;
  

• 重新创建主键及关联索引：

  ALTER TABLE SCHEME.IP_LOG_TABLE ADD CONSTRAINT PK_IP_LOG_ID PRIMARY KEY (ID);
  

5. 完成修复后执行删除操作 #

索引恢复正常后，建议分批删除旧数据（避免大事务引发新问题）：

DECLARE
  v_rows_deleted NUMBER;
BEGIN
  LOOP
    DELETE FROM SCHEME.IP_LOG_TABLE 
    WHERE LOG_DATE <= SYSDATE - INTERVAL '2' YEAR
    AND ROWNUM <= 1000;
    v_rows_deleted := SQL%ROWCOUNT;
    COMMIT;
    EXIT WHEN v_rows_deleted = 0;
  END LOOP;
END;
/

内容的提问来源于stack exchange，提问作者Pavel Trostianko