在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

此外，在对表进行调查之后，我们发现成功插入的记录的所有主键在停止之前都是顺序的。

另一个进程在后台运行得很好，我们可以看到插入的记录也是顺序的，但是在存储过程插入的最后一个记录和由另一个进程插入的下一个记录之间有一个数字间隔。

这意味着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多次使用来自seuquence的相同数字。HTH