在 PL/SQL 中调查 ORA-00001

Question

我们上次在生产环境中执行存储过程时遇到了 ORA-00001。 存储过程直到昨天才正常工作，我尝试进行故障排除，但没有取得任何进展。

如果您能在下面提供一些有用的想法，我们将不胜感激。

我将情况分解为： 1( 带主键的表， 2( 一个序列 3( 存储过程。

1(我们有如下主表：

CREATE TABLE MY_MESSAGES (MESSAGE_ID NUMBER, MESSAGE VARCHAR2(200));
CREATE UNIQUE INDEX MY_MESSAGES_PK ON MY_MESSAGES (MESSAGE_ID);
ALTER TABLE MY_MESSAGES ADD CONSTRAINT MY_MESSAGES_PK PRIMARY KEY (MESSAGE_ID) USING INDEX  ENABLE;

2( 一个序列

CREATE SEQUENCE  MESSAGE_ID_SEQUENCE;

独立的备份表：

CREATE TABLE MY_MESSAGES_BKP (BKP_ID VARCHAR2(200), RECIVED_TIME TIMESTAMP, MESSAGE VARCHAR2(200));
INSERT INTO MY_MESSAGES_BKP VALUES('201', TIMESTAMP '2018-09-26 00:00:00.000000', 'MSG206');
INSERT INTO MY_MESSAGES_BKP VALUES('202', TIMESTAMP '2018-09-26 05:00:00.000000', 'MSG206');
INSERT INTO MY_MESSAGES_BKP VALUES('203', TIMESTAMP '2018-09-26 06:00:00.000000', 'MSG207');
INSERT INTO MY_MESSAGES_BKP VALUES('204', TIMESTAMP '2018-09-26 07:00:00.000000', 'MSG208');
INSERT INTO MY_MESSAGES_BKP VALUES('205', TIMESTAMP '2018-09-26 08:00:00.000000', 'MSG209');
COMMIT;

3(最后，存储过程：

DECLARE
TYPE VARCHAR_TABLE IS TABLE OF VARCHAR(200);
V_MESSAGE_ID NUMBER(20) := 0;
V_BKP_IDS VARCHAR_TABLE := VARCHAR_TABLE();
V_EXC_QUERY VARCHAR2(200) := 'INSERT INTO MY_MESSAGES(MESSAGE_ID, MESSAGE) SELECT :1, MESSAGE FROM  MY_MESSAGES_BKP WHERE BKP_ID = :2';
BEGIN
SELECT BKP_ID BULK COLLECT INTO V_BKP_IDS FROM MY_MESSAGES_BKP WHERE RECIVED_TIME > TIMESTAMP '2018-09-26 00:00:00.000000';
FOR I IN 1..V_BKP_IDS.COUNT LOOP
EXECUTE IMMEDIATE 'SELECT MESSAGE_ID_SEQUENCE.NEXTVAL FROM DUAL' INTO V_MESSAGE_ID ;
EXECUTE IMMEDIATE V_EXC_QUERY USING V_MESSAGE_ID, V_BKP_IDS(I);
END LOOP;
V_BKP_IDS.DELETE;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM); 
END;
/

知道这一点后，表MY_MESSAGES由对主键使用相同的序列 (MESSAGE_ID_SEQUENCE( 的其他进程使用。

存储过程运行了一段时间，它从 ~5000 条记录中插入了 400 多条记录......然后它停止并出现以下错误：

ORA-00001: unique constraint my_messages_pk) violated

此外，在调查该表后，我们发现成功插入的记录的所有主键在停止之前都是连续的。

另一个进程在后台运行良好，我们可以看到插入的记录也是连续的，但存储过程插入的最后一个记录与另一个进程插入的下一个记录之间有 1 个数字差距。

这意味着MESSAGE_ID_SEQUENCE。NEXTVAL 由存储过程执行，但没有插入任何记录。

当存储过程停止时，表没有带主键的记录。

可能出现什么问题？ 我们如何进一步调查？

Answer 1

最直接的解释是，在ORA-00001的情况下，这个选择SELECT :1, MESSAGE FROM MY_MESSAGES_BKP WHERE BKP_ID = :2找到不止一行带有BKP_ID = :2。这反过来会导致插入INSERT INTO MY_MESSAGES(MESSAGE_ID, MESSAGE) SELECT :1, MESSAGE FROM MY_MESSAGES_BKP WHERE BKP_ID = :2多次使用同。呵呵