Hi,
I'm currently facing ORA-32044.
I tried to use below query to find the offending records, but the table in production DB has around 10 million records. Is there any other approach to find the offending records causing this error?
CREATE TABLE mi_enty_rels_bkp (ENRL_KEY NUMBER, PRED_ENTY_KEY NUMBER, SUCC_ENTY_KEY NUMBER);
INSERT INTO MI_ENTY_RELS_BKP (ENRL_KEY, PRED_ENTY_KEY, SUCC_ENTY_KEY) VALUES (1, 10, 11);
INSERT INTO MI_ENTY_RELS_BKP (ENRL_KEY, PRED_ENTY_KEY, SUCC_ENTY_KEY) VALUES (2, 11, 12);
INSERT INTO MI_ENTY_RELS_BKP (ENRL_KEY, PRED_ENTY_KEY, SUCC_ENTY_KEY) VALUES (3, 12, 10);
INSERT INTO MI_ENTY_RELS_BKP (ENRL_KEY, PRED_ENTY_KEY, SUCC_ENTY_KEY) VALUES (4, 13, 14);
INSERT INTO MI_ENTY_RELS_BKP (ENRL_KEY, PRED_ENTY_KEY, SUCC_ENTY_KEY) VALUES (5, 14, 15);
INSERT INTO MI_ENTY_RELS_BKP (ENRL_KEY, PRED_ENTY_KEY, SUCC_ENTY_KEY) VALUES (6, 11, 12);
INSERT INTO MI_ENTY_RELS_BKP (ENRL_KEY, PRED_ENTY_KEY, SUCC_ENTY_KEY) VALUES (7, 12, 13);
INSERT INTO MI_ENTY_RELS_BKP (ENRL_KEY, PRED_ENTY_KEY, SUCC_ENTY_KEY) VALUES (8, 13, 11);
WITH EntyCTE (enrl_key, pred_enty_key,succ_enty_key,level1,match_count)
AS
(
SELECT enrl_key, pred_enty_key,succ_enty_key, 0, 0 match_count
FROM mi_enty_rels_bkp enty
WHERE succ_enty_key != pred_enty_key
and succ_enty_key = 11
UNION ALL
SELECT enty.enrl_key, enty.pred_enty_key,enty.succ_enty_key, m.level1 + 1,1 match_count
FROM mi_enty_rels_bkp enty
INNER JOIN EntyCTE m
ON enty.succ_enty_key = m.pred_enty_key
where m.match_count=0
)
SELECT * FROM EntyCTE;
Working on Windows 7
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
Thanks