Значение последовательности Oracle увеличивается при нарушении уникального ограничения

Это нормальное поведение для последовательностей. У вас не гарантируется непрерывная последовательность. Поскольку может выполняться несколько транзакций, некоторые из которых фиксируются, а некоторые откатываются, я даже не уверен, как у вас может быть последовательность, которая давала бы вам непрерывную последовательность, как будто это звучит так, как вы хотите.

Если вам действительно нужна такая непрерывная последовательность, вы должны убедиться, что она увеличивается в той же транзакции:

CREATE TABLE my_id ( col1 NUMBER );
INSERT INTO my_id ( col1 ) VALUES ( 0 );

Затем выполните транзакцию:

DECLARE
    id NUMBER;
BEGIN
  UPDATE my_id SET col1 = col1 + 1
  RETURNING col1 INTO id;

  INSERT INTO mobile_app ... whatever ...

  COMMIT;
END;

Большая проблема в том, что таблица идентификаторов становится узким местом. Вы не можете одновременно вставлять в таблицу mobile_app из разных сессий.

Я очень ценю все предоставленные комментарии и ответы! Основываясь на предоставленной информации, я думаю, что лучший подход - просто оставить все как есть и ожидать, что последовательность не останется без пробелов. Похоже, что у любого другого действия слишком много потенциальных недостатков.

1. Вы можете просто вставить порядковый номер в оператор вставки.

   INSERT INTO MOBILE_APP (MOBILE_APP_ID, name, description) VALUES (MOBILE_APP_ID_SEQ.NEXTVAL, 'Name', 'Desc');

2. Если вы используете другое приложение, сделайте эту вставку, сначала получите идентификатор, затем перейдите в состояние вставки и другой процесс.

      --Create Id.
      CREATE OR REPLACE PROCEDURE MOBILE_APP_ID
      (MOBILE_APP_ID OUT SYS_REFCURSOR )
      IS
         BEGIN
             open MOBILE_APP_ID for 
          SELECT MOBILE_APP_ID_SEQ.NEXTVAL MOBILE_APP_ID
          FROM dual;
   
      END ;
   
     --Then get the id.
     int id = MOBILE_APP_ID.MOBILE_APP_ID;
   
    --Then insert the id.
    INSERT INTO MOBILE_APP (MOBILE_APP_ID,name, description) VALUES (id  ,'Name', 'Desc');
   

Как уже упоминалось, последовательности являются последовательностями и не подходят, если вам нужна последовательность без пропусков. Сначала подумайте: уверены ли вы, что вам нужна непрерывная последовательность? В таком случае ни одно из предложенных решений не на 100% безопасно от разрывов. У обоих будут проблемы, если другой сеанс получит идентификатор до того, как первый сеанс зафиксирует свои изменения. Тогда второй сеанс будет иметь тот же идентификатор, что и первый, и это, конечно, вызовет проблемы. Риск для этого не должен быть таким большим, но он все равно существует, особенно если у вас много активных сеансов.

Непросто получить последовательность без пропусков. Один из способов может быть, например, использовать что-то вроде предложенного табличного решения, а затем перехватить любые повторяющиеся ключевые ошибки, а затем повторить попытку вставить его. Не красиво и неэффективно, но задача должна быть выполнена.

Я думаю, мне не нужно создавать здесь какую-либо последовательность или что-то еще, кроме приведенного ниже кода

CREATE OR REPLACE TRIGGER AUTO_APPID
BEFORE INSERT
ON MOBILE_APP
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
begin

SELECT NVL(MAX(MOBILE_APP_ID),0)+1 INTO :NEW.MOBILE_APP_ID FROM MOBILE_APP;

END ;