Oracle 重復記錄檢測與清理三大法：ROWID 自關聯、GROUP BY HAVING、EXCEPTIONS 表

觸發場景

創建唯一索引報錯：ORA-01452: cannot create unique index; duplicate keys found。需先定位並清理重復行。

方法一：ROWID 自關聯 (單表、利用偽列)

-- 保留 ROWID 最大 (最新) 的行，刪除其餘
DELETE FROM code_ref a
WHERE  rowid < (SELECT MAX(rowid)
                FROM   code_ref b
                WHERE  b.pk_col1 = a.pk_col1
                AND    b.pk_col2 = a.pk_col2);  -- 唯一鍵列組合

原理：同一鍵值的行 ROWID 不同，保留最大值即保留最後插入的一行。

方法二：GROUP BY / HAVING (標準 SQL、可移植)

-- 1. 定位重復鍵值
SELECT pk_col1, pk_col2, COUNT(*)
FROM   code_ref
GROUP BY pk_col1, pk_col2
HAVING COUNT(*) > 1;

-- 2. 刪除重復 (保留 MIN rowid)
DELETE FROM code_ref
WHERE  rowid NOT IN (
  SELECT MIN(rowid)
  FROM   code_ref
  GROUP BY pk_col1, pk_col2
);

方法三：EXCEPTIONS INTO 子句 (索引創建過程中自動收集)

-- 1. 創建 EXCEPTIONS 表 (腳本 $ORACLE_HOME/rdbms/admin/utlexcpt.sql)
@?/rdbms/admin/utlexcpt.sql  -- 創建 EXCEPTIONS 表

-- 2. 嘗試創建索引，將違規行導入 EXCEPTIONS
ALTER TABLE code_ref ADD CONSTRAINT pk_code_ref PRIMARY KEY (pk_col1, pk_col2)
EXCEPTIONS INTO EXCEPTIONS;
-- 報錯 ORA-02299: cannot validate - duplicate keys found
-- 但 EXCEPTIONS 表已填入 ROWID

-- 3. 關聯刪除
DELETE FROM code_ref
WHERE  rowid IN (SELECT row_id FROM EXCEPTIONS);
-- 或保留一行：需配合 ROWID 比較

三方法對比

清理後驗證

-- 確認無重復
SELECT pk_col1, pk_col2, COUNT(*)
FROM   code_ref
GROUP BY pk_col1, pk_col2
HAVING COUNT(*) > 1;
-- 無行返回 = 清理完成

-- 正常創建唯一索引
CREATE UNIQUE INDEX idx_code_ref_pk ON code_ref(pk_col1, pk_col2);