Как сбросить последовательность в Oracle?

Настоящий перезапуск невозможен Насколько мне известно. (Пожалуйста, поправьте меня, если я ошибаюсь!).

Однако, если вы хотите установить его на 0, вы можете просто удалить и воссоздать его.

Если вы хотите установить для него определенное значение, вы можете установить для INCREMENT отрицательное значение и получить следующее значение.

То есть, если ваша последовательность равна 500, вы можете установить ее на 100 с помощью

ALTER SEQUENCE serial INCREMENT BY -400;
SELECT serial.NEXTVAL FROM dual;
ALTER SEQUENCE serial INCREMENT BY 1;

Изменение значения INCREMENT последовательности, его увеличение и последующее изменение довольно безболезненно, плюс у вас есть дополнительное преимущество, заключающееся в том, что вам не нужно повторно устанавливать все права доступа, как если бы вы отбросили / воссоздали последовательность.

Вот хорошая процедура сброса любой последовательности на 0 от гуру Oracle Том Кайт. Большое обсуждение плюсов и минусов также по ссылкам ниже.

[email protected]> 
create or replace
procedure reset_seq( p_seq_name in varchar2 )
is
    l_val number;
begin
    execute immediate
    'select ' || p_seq_name || '.nextval from dual' INTO l_val;

    execute immediate
    'alter sequence ' || p_seq_name || ' increment by -' || l_val || 
                                                          ' minvalue 0';

    execute immediate
    'select ' || p_seq_name || '.nextval from dual' INTO l_val;

    execute immediate
    'alter sequence ' || p_seq_name || ' increment by 1 minvalue 0';
end;
/

На этой странице: Динамический SQL для сброса значения последовательности
Еще одно хорошее обсуждение также здесь: Как сбросить последовательности?

Этот хранимая процедура перезапускает мою последовательность:

Create or Replace Procedure Reset_Sequence  
  is
  SeqNbr Number;
begin
   /*  Reset Sequence 'seqXRef_RowID' to 0    */
   Execute Immediate 'Select seqXRef.nextval from dual ' Into SeqNbr;
   Execute Immediate 'Alter sequence  seqXRef increment by - ' || TO_CHAR(SeqNbr) ;
   Execute Immediate 'Select seqXRef.nextval from dual ' Into SeqNbr;
   Execute Immediate 'Alter sequence  seqXRef increment by 1';
END;

/

1) Предположим, вы создали ПОСЛЕДОВАТЕЛЬНОСТЬ, как показано ниже:

CREATE SEQUENCE TESTSEQ
INCREMENT BY 1
MINVALUE 1
MAXVALUE 500
NOCACHE
NOCYCLE
NOORDER

2) Теперь вы получаете значения из ПОСЛЕДОВАТЕЛЬНОСТИ. Допустим, я получал четыре раза, как показано ниже.

SELECT TESTSEQ.NEXTVAL FROM dual
SELECT TESTSEQ.NEXTVAL FROM dual
SELECT TESTSEQ.NEXTVAL FROM dual
SELECT TESTSEQ.NEXTVAL FROM dual

3) После выполнения вышеупомянутых четырех команд значение ПОСЛЕДОВАТЕЛЬНОСТИ будет 4. Теперь предположим, что я снова сбросил значение ПОСЛЕДОВАТЕЛЬНОСТИ на 1. Следуйте следующим шагам. Выполните все шаги в том же порядке, как показано ниже:

1. ALTER SEQUENCE TESTSEQ INCREMENT BY -3;
2. SELECT TESTSEQ.NEXTVAL FROM dual
3. ALTER SEQUENCE TESTSEQ INCREMENT BY 1;
4. SELECT TESTSEQ.NEXTVAL FROM dual

Это мой подход:

1. отбросить последовательность
2. воссоздать это

Пример:

--Drop sequence

DROP SEQUENCE MY_SEQ;

-- Create sequence 

create sequence MY_SEQ
minvalue 1
maxvalue 999999999999999999999
start with 1
increment by 1
cache 20;

Следующий скрипт устанавливает желаемое значение для последовательности:

Учитывая только что созданную последовательность с именем PCS_PROJ_KEY_SEQ и таблицу PCS_PROJ:

BEGIN
   DECLARE
      PROJ_KEY_MAX       NUMBER := 0;
      PROJ_KEY_CURRVAL   NUMBER := 0;
   BEGIN

    SELECT MAX (PROJ_KEY) INTO PROJ_KEY_MAX FROM PCS_PROJ;
    EXECUTE IMMEDIATE 'ALTER SEQUENCE PCS_PROJ_KEY_SEQ INCREMENT BY ' || PROJ_KEY_MAX;
    SELECT PCS_PROJ_KEY_SEQ.NEXTVAL INTO PROJ_KEY_CURRVAL FROM DUAL;
    EXECUTE IMMEDIATE 'ALTER SEQUENCE PCS_PROJ_KEY_SEQ INCREMENT BY 1';

END;
END;
/

Мой подход - крохотное расширение Пример Дугмана.

Расширения ...

Передайте начальное значение в качестве параметра. Почему? Мне нравится называть вещь, сбрасывающую последовательность обратно в максимальный идентификатор, используемый в какой-либо таблице. В итоге я вызываю этот процесс из другого скрипта, который выполняет несколько вызовов для целой группы последовательностей, сбрасывая nextval обратно на некоторый уровень, который достаточно высок, чтобы не вызывать нарушений первичного ключа, когда я использую значение последовательности для уникального идентификатора.

Он также учитывает предыдущий minvalue. Фактически, это может быть подтолкнуть следующее значение еще выше, если желаемое p_val или существующее минимальное значение выше, чем текущее или вычисленное следующее значение.

Лучше всего то, что его можно вызвать для сброса до указанного значения и просто подождать, пока вы не увидите в конце процедуру оболочки «исправить все мои последовательности».

create or replace
procedure Reset_Sequence( p_seq_name in varchar2, p_val in number default 0)
is
  l_current number := 0;
  l_difference number := 0;
  l_minvalue user_sequences.min_value%type := 0;

begin

  select min_value
  into l_minvalue
  from user_sequences
  where sequence_name = p_seq_name;

  execute immediate
  'select ' || p_seq_name || '.nextval from dual' INTO l_current;

  if p_Val < l_minvalue then
    l_difference := l_minvalue - l_current;
  else
    l_difference := p_Val - l_current;
  end if;

  if l_difference = 0 then
    return;
  end if;

  execute immediate
    'alter sequence ' || p_seq_name || ' increment by ' || l_difference || 
       ' minvalue ' || l_minvalue;

  execute immediate
    'select ' || p_seq_name || '.nextval from dual' INTO l_difference;

  execute immediate
    'alter sequence ' || p_seq_name || ' increment by 1 minvalue ' || l_minvalue;
end Reset_Sequence;

Эта процедура полезна сама по себе, но теперь давайте добавим еще одну, которая вызывает ее и задает все программно с соглашением об именах последовательностей и ищет максимальное значение, используемое в существующей таблице / поле ...

create or replace
procedure Reset_Sequence_to_Data(
  p_TableName varchar2,
  p_FieldName varchar2
)
is
  l_MaxUsed NUMBER;
BEGIN

  execute immediate
    'select coalesce(max(' || p_FieldName || '),0) from '|| p_TableName into l_MaxUsed;

  Reset_Sequence( p_TableName || '_' || p_Fieldname || '_SEQ', l_MaxUsed );

END Reset_Sequence_to_Data;

Теперь готовим на газе!

Приведенная выше процедура проверяет максимальное значение поля в таблице, строит имя последовательности из пары таблица / поле и вызывает "Reset_Sequence" с этим максимальным значением.

Далее идет последний кусок этой головоломки и глазурь на торте ...

create or replace
procedure Reset_All_Sequences
is
BEGIN

  Reset_Sequence_to_Data( 'ACTIVITYLOG', 'LOGID' );
  Reset_Sequence_to_Data( 'JOBSTATE', 'JOBID' );
  Reset_Sequence_to_Data( 'BATCH', 'BATCHID' );

END Reset_All_Sequences;

В моей реальной базе данных есть около сотни других последовательностей, сбрасываемых с помощью этого механизма, поэтому в этой процедуре, описанной выше, есть еще 97 вызовов Reset_Sequence_to_Data.

Любить это? Ненавижу это? В разных?

Есть еще один способ сбросить последовательность в Oracle: установить свойства maxvalue и cycle. Когда nextval последовательности попадает в maxvalue, если свойство cycle установлено, тогда она снова начнется с minvalue последовательности.

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

Значение maxvalue должно быть больше текущего nextval, поэтому приведенная ниже процедура включает необязательный параметр, разрешающий буфер в случае повторного доступа к последовательности между выбором nextval в процедуре и установкой свойства cycle.

create sequence s start with 1 increment by 1;

select s.nextval from dual
connect by level <= 20;

   NEXTVAL
----------
         1 
...
        20

create or replace procedure reset_sequence ( i_buffer in pls_integer default 0)
as
  maxval pls_integer;
begin

  maxval := s.nextval + greatest(i_buffer, 0); --ensure we don't go backwards!
  execute immediate 'alter sequence s cycle minvalue 0 maxvalue ' || maxval;
  maxval := s.nextval;
  execute immediate 'alter sequence s nocycle maxvalue 99999999999999';

end;
/
show errors

exec reset_sequence;

select s.nextval from dual;

   NEXTVAL
----------
         1 

Процедура в существующем виде по-прежнему допускает возможность того, что другой сеанс получит значение 0, что может или не может быть проблемой для вас. Если это так, вы всегда можете:

• Установите minvalue 1 в первую очередь
• Исключить вторую выборку nextval
• Переместите оператор, чтобы установить свойство nocycle в другую процедуру, которая будет запущена позже (при условии, что вы хотите это сделать).

alter sequence serial restart start with 1;

Эта функция была официально добавлена ​​в 18c, но неофициально доступна с 12.1.

Возможно, безопасно использовать эту недокументированную функцию в 12.1. Несмотря на то, что синтаксис нет включен в официальная документация, он генерируется пакетом Oracle DBMS_METADATA_DIFF. Я использовал его несколько раз в производственных системах. Однако я создал запрос службы Oracle, и они подтвердили, что это не ошибка документации, функция действительно не поддерживается.

В 18c эта функция не отображается в синтаксисе языка SQL, но включена в Руководство администратора базы данных.

Вы можете использовать опцию CYCLE, показанную ниже:

CREATE SEQUENCE test_seq
MINVALUE 0
MAXVALUE 100
START WITH 0
INCREMENT BY 1
CYCLE;

В этом случае, когда последовательность достигает MAXVALUE (100), она возвращается к MINVALUE (0).

В случае декрементированной последовательности последовательность вернется к MAXVALUE.

Я создаю блок для сброса всех моих последовательностей:

DECLARE
    I_val number;
BEGIN
    FOR US IN
        (SELECT US.SEQUENCE_NAME FROM USER_SEQUENCES US)
    LOOP
        execute immediate 'select ' || US.SEQUENCE_NAME || '.nextval from dual' INTO l_val;
        execute immediate 'alter sequence ' || US.SEQUENCE_NAME || ' increment by -' || l_val || ' minvalue 0';
        execute immediate 'select ' || US.SEQUENCE_NAME || '.nextval from dual' INTO l_val;
        execute immediate 'alter sequence ' || US.SEQUENCE_NAME || ' increment by 1 minvalue 0';
    END LOOP;
END;

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

• Во-первых, он защищает от атак SQL-инъекций, поскольку ни одна из переданных строк не используется для непосредственного создания каких-либо динамических операторов SQL,
• Во-вторых, он предотвращает установку следующего значения последовательности за пределы минимального или максимального значений последовательности. next_value будет! = min_value и между min_value и max_value.
• В-третьих, при очистке учитываются текущие (или предлагаемые) настройки increment_by, а также все другие настройки последовательности.
• В-четвертых, все параметры, кроме первого, являются необязательными и, если не указано иное, принимают текущие настройки последовательности по умолчанию. Если не указаны необязательные параметры, никакие действия не предпринимаются.
• Наконец, если вы попытаетесь изменить последовательность, которая не существует (или не принадлежит текущему пользователю), это вызовет ошибку ORA-01403: no data found.

Вот код:

CREATE OR REPLACE PROCEDURE alter_sequence(
    seq_name      user_sequences.sequence_name%TYPE
  , next_value    user_sequences.last_number%TYPE := null
  , increment_by  user_sequences.increment_by%TYPE := null
  , min_value     user_sequences.min_value%TYPE := null
  , max_value     user_sequences.max_value%TYPE := null
  , cycle_flag    user_sequences.cycle_flag%TYPE := null
  , cache_size    user_sequences.cache_size%TYPE := null
  , order_flag    user_sequences.order_flag%TYPE := null)
  AUTHID CURRENT_USER
AS
  l_seq user_sequences%rowtype;
  l_old_cache user_sequences.cache_size%TYPE;
  l_next user_sequences.min_value%TYPE;
BEGIN
  -- Get current sequence settings as defaults
  SELECT * INTO l_seq FROM user_sequences WHERE sequence_name = seq_name;

  -- Update target settings
  l_old_cache := l_seq.cache_size;
  l_seq.increment_by := nvl(increment_by, l_seq.increment_by);
  l_seq.min_value    := nvl(min_value, l_seq.min_value);
  l_seq.max_value    := nvl(max_value, l_seq.max_value);
  l_seq.cycle_flag   := nvl(cycle_flag, l_seq.cycle_flag);
  l_seq.cache_size   := nvl(cache_size, l_seq.cache_size);
  l_seq.order_flag   := nvl(order_flag, l_seq.order_flag);

  IF next_value is NOT NULL THEN
    -- Determine next value without exceeding limits
    l_next := LEAST(GREATEST(next_value, l_seq.min_value+1),l_seq.max_value);

    -- Grab the actual latest seq number
    EXECUTE IMMEDIATE
        'ALTER SEQUENCE '||l_seq.sequence_name
            || ' INCREMENT BY 1'
            || ' MINVALUE '||least(l_seq.min_value,l_seq.last_number-l_old_cache)
            || ' MAXVALUE '||greatest(l_seq.max_value,l_seq.last_number)
            || ' NOCACHE'
            || ' ORDER';
    EXECUTE IMMEDIATE 
      'SELECT '||l_seq.sequence_name||'.NEXTVAL FROM DUAL'
    INTO l_seq.last_number;

    l_next := l_next-l_seq.last_number-1;

    -- Reset the sequence number
    IF l_next <> 0 THEN
      EXECUTE IMMEDIATE 
        'ALTER SEQUENCE '||l_seq.sequence_name
            || ' INCREMENT BY '||l_next
            || ' MINVALUE '||least(l_seq.min_value,l_seq.last_number)
            || ' MAXVALUE '||greatest(l_seq.max_value,l_seq.last_number)
            || ' NOCACHE'
            || ' ORDER';
      EXECUTE IMMEDIATE 
        'SELECT '||l_seq.sequence_name||'.NEXTVAL FROM DUAL'
      INTO l_next;
    END IF;
  END IF;

  -- Prepare Sequence for next use.
  IF COALESCE( cycle_flag
             , next_value
             , increment_by
             , min_value
             , max_value
             , cache_size
             , order_flag) IS NOT NULL
  THEN
    EXECUTE IMMEDIATE 
      'ALTER SEQUENCE '||l_seq.sequence_name
          || ' INCREMENT BY '||l_seq.increment_by
          || ' MINVALUE '||l_seq.min_value
          || ' MAXVALUE '||l_seq.max_value
          || CASE l_seq.cycle_flag
             WHEN 'Y' THEN ' CYCLE' ELSE ' NOCYCLE' END
          || CASE l_seq.cache_size
             WHEN 0 THEN ' NOCACHE'
             ELSE ' CACHE '||l_seq.cache_size END
          || CASE l_seq.order_flag
             WHEN 'Y' THEN ' ORDER' ELSE ' NOORDER' END;
  END IF;
END;

В моем проекте однажды случилось так, что кто-то вручную ввел записи без использования последовательности, поэтому мне пришлось вручную сбросить значение последовательности, для чего я написал ниже фрагмент кода sql:

declare
max_db_value number(10,0);
cur_seq_value number(10,0);
counter number(10,0);
difference number(10,0);
dummy_number number(10);

begin

-- enter table name here
select max(id) into max_db_value from persons;
-- enter sequence name here
select last_number into cur_seq_value from user_sequences where  sequence_name = 'SEQ_PERSONS';

difference  := max_db_value - cur_seq_value;

 for counter in 1..difference
 loop
    -- change sequence name here as well
    select SEQ_PERSONS.nextval into dummy_number from dual;
 end loop;
end;

Обратите внимание, что приведенный выше код будет работать, если последовательность отстает.

Я предлагаю альтернативу тому, что пользователю не нужно знать значения, система получает и использует переменные для обновления.

--Atualizando sequence da tabela SIGA_TRANSACAO, pois está desatualizada
DECLARE
 actual_sequence_number INTEGER;
 max_number_from_table INTEGER;
 difference INTEGER;
BEGIN
 SELECT [nome_da_sequence].nextval INTO actual_sequence_number FROM DUAL;
 SELECT MAX([nome_da_coluna]) INTO max_number_from_table FROM [nome_da_tabela];
 SELECT (max_number_from_table-actual_sequence_number) INTO difference FROM DUAL;
IF difference > 0 then
 EXECUTE IMMEDIATE CONCAT('alter sequence [nome_da_sequence] increment by ', difference);
 --aqui ele puxa o próximo valor usando o incremento necessário
 SELECT [nome_da_sequence].nextval INTO actual_sequence_number from dual;
--aqui volta o incremento para 1, para que futuras inserções funcionem normalmente
 EXECUTE IMMEDIATE 'ALTER SEQUENCE [nome_da_sequence] INCREMENT by 1';
 DBMS_OUTPUT.put_line ('A sequence [nome_da_sequence] foi atualizada.');
ELSE
 DBMS_OUTPUT.put_line ('A sequence [nome_da_sequence] NÃO foi atualizada, já estava OK!');
END IF;
END;

Вот как сделать так, чтобы все последовательности автоинкремента соответствовали фактическим данным:

1. Создайте процедуру для принудительного применения следующего значения, как уже было описано в этом потоке:

   CREATE OR REPLACE PROCEDURE Reset_Sequence(
       P_Seq_Name IN VARCHAR2,
       P_Val      IN NUMBER DEFAULT 0)
   IS
     L_Current    NUMBER                      := 0;
     L_Difference NUMBER                      := 0;
     L_Minvalue User_Sequences.Min_Value%Type := 0;
   BEGIN
     SELECT Min_Value
     INTO L_Minvalue
     FROM User_Sequences
     WHERE Sequence_Name = P_Seq_Name;
     EXECUTE Immediate 'select ' || P_Seq_Name || '.nextval from dual' INTO L_Current;
     IF P_Val        < L_Minvalue THEN
       L_Difference := L_Minvalue - L_Current;
     ELSE
       L_Difference := P_Val - L_Current;
     END IF;
     IF L_Difference = 0 THEN
       RETURN;
     END IF;
     EXECUTE Immediate 'alter sequence ' || P_Seq_Name || ' increment by ' || L_Difference || ' minvalue ' || L_Minvalue;
     EXECUTE Immediate 'select ' || P_Seq_Name || '.nextval from dual' INTO L_Difference;
     EXECUTE Immediate 'alter sequence ' || P_Seq_Name || ' increment by 1 minvalue ' || L_Minvalue;
   END Reset_Sequence;
   

2. Создайте еще одну процедуру для согласования всех последовательностей с фактическим содержанием:

   CREATE OR REPLACE PROCEDURE RESET_USER_SEQUENCES_TO_DATA
   IS
     STMT CLOB;
   BEGIN
     SELECT 'select ''BEGIN'' || chr(10) || x || chr(10) || ''END;'' FROM (select listagg(x, chr(10)) within group (order by null) x FROM ('
       || X
       || '))'
     INTO STMT
     FROM
       (SELECT LISTAGG(X, ' union ') WITHIN GROUP (
       ORDER BY NULL) X
       FROM
         (SELECT CHR(10)
           || 'select ''Reset_Sequence('''''
           || SEQ_NAME
           || ''''','' || coalesce(max('
           || COL_NAME
           || '), 0) || '');'' x from '
           || TABLE_NAME X
         FROM
           (SELECT TABLE_NAME,
             REGEXP_SUBSTR(WTEXT, 'NEW\.(\S*) IS NULL',1,1,'i',1) COL_NAME,
             REGEXP_SUBSTR(BTEXT, '(\.|\s)([a-z_]*)\.nextval',1,1,'i',2) SEQ_NAME
           FROM USER_TRIGGERS
           LEFT JOIN
             (SELECT NAME BNAME,
               TEXT BTEXT
             FROM USER_SOURCE
             WHERE TYPE = 'TRIGGER'
             AND UPPER(TEXT) LIKE '%NEXTVAL%'
             )
           ON BNAME = TRIGGER_NAME
           LEFT JOIN
             (SELECT NAME WNAME,
               TEXT WTEXT
             FROM USER_SOURCE
             WHERE TYPE = 'TRIGGER'
             AND UPPER(TEXT) LIKE '%IS NULL%'
             )
           ON WNAME             = TRIGGER_NAME
           WHERE TRIGGER_TYPE   = 'BEFORE EACH ROW'
           AND TRIGGERING_EVENT = 'INSERT'
           )
         )
       ) ;
     EXECUTE IMMEDIATE STMT INTO STMT;
     --dbms_output.put_line(stmt);
     EXECUTE IMMEDIATE STMT;
   END RESET_USER_SEQUENCES_TO_DATA;
   

ПРИМЕЧАНИЯ:

1. Процедура извлекает имена из кода триггера и не зависит от соглашений об именах.
2. Чтобы проверить сгенерированный код перед выполнением, переключите комментарии в последних двух строках

Джезус, все это программирование для перезагрузки индекса ... Возможно, я идиот, но для pre-oracle 12 (в котором есть функция перезапуска), что не так с simpel:

drop sequence blah;
create sequence blah 

?

Сохраненная процедура, которая сработала для меня

create or replace
procedure reset_sequence( p_seq_name in varchar2, tablename in varchar2 )
is
    l_val number;
    maxvalueid number;
begin
    execute immediate 'select ' || p_seq_name || '.nextval from dual' INTO l_val;
    execute immediate 'select max(id) from ' || tablename INTO maxvalueid;
    execute immediate 'alter sequence ' || p_seq_name || ' increment by -' || l_val || ' minvalue 0';
    execute immediate 'select ' || p_seq_name || '.nextval from dual' INTO l_val;
    execute immediate 'alter sequence ' || p_seq_name || ' increment by '|| maxvalueid ||' minvalue 0';  
    execute immediate 'select ' || p_seq_name || '.nextval from dual' INTO l_val;
    execute immediate 'alter sequence ' || p_seq_name || ' increment by 1 minvalue 0';
end;

Как использовать хранимую процедуру:

execute reset_sequence('company_sequence','company');