Oracle에서 시퀀스를 리셋하려면 어떻게 해야 합니까?
포스트그레SQL은 다음과 같은 작업을 수행할 수 있습니다.
ALTER SEQUENCE serial RESTART WITH 0;
Oracle과 동등한 제품이 있습니까?
다음은 Oracle guru Tom Kyte에서 시퀀스를 0으로 리셋하는 좋은 절차입니다.아래 링크에서도 장점과 단점에 대한 훌륭한 토론입니다.
tkyte@TKYTE901.US.ORACLE.COM>
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
또 다른 유익한 논의도 여기에 있습니다.시퀀스를 리셋하는 방법
AFAIK 를 정상적으로 재기동할 수 없습니다.(잘못하면 정정해 주세요!)
그러나 0으로 설정하려면 삭제한 후 재생성하면 됩니다.
특정 값으로 설정할 경우 INCREMENT를 음의 값으로 설정하고 다음 값을 얻을 수 있습니다.
즉, 시퀀스가 500일 경우,
ALTER SEQUENCE serial INCREMENT BY -400;
SELECT serial.NEXTVAL FROM dual;
ALTER SEQUENCE serial INCREMENT BY 1;
정규 시퀀스의 경우:
alter sequence serial restart start with 1;
ID 열에 사용되는 시스템 생성 시퀀스의 경우:
alter table table_name modify id generated by default on null as identity(start with 1);
이 기능은 18c에 공식적으로 추가되었지만 12.1부터 비공식적으로 사용할 수 있습니다.
이 문서화되어 있지 않은 기능은 12.1에서 사용하는 것이 안전합니다.구문은 공식 문서에 포함되어 있지 않지만 Oracle 패키지 DBMS_METADA에 의해 생성됩니다.TA_DIFF.생산 시스템에서 여러 번 사용해 본 적이 있습니다.그러나 Oracle Service 요청을 작성했더니 문서 버그가 아닌 것으로 확인되었습니다.이 기능은 실제로 지원되지 않습니다.
18c 에서는 이 기능은 SQL Language Syntax에는 표시되지 않지만 Database Administrator's Guide에는 포함되어 있습니다.
이것이 저의 접근법입니다.
- 순서를 취소하다
- 그것을 재현하다
예:
--Drop sequence
DROP SEQUENCE MY_SEQ;
-- Create sequence
create sequence MY_SEQ
minvalue 1
maxvalue 999999999999999999999
start with 1
increment by 1
cache 20;
내 접근법은 더그먼의 사례를 약간 확장한 것이다.
내선번호는...
시드 값을 매개 변수로 전달합니다. 왜요?저는 어떤 테이블에서 사용되는 최대 ID로 시퀀스를 재설정하는 것을 호출하고 싶습니다.시퀀스 전체에 대해 여러 콜을 실행하는 다른 스크립트에서 이 proc를 호출하여 시퀀스 값을 고유 식별자로 사용하는 프라이머리 키 위반을 일으키지 않을 정도로 nextval을 리셋합니다.
또한 이전 최소값도 적용됩니다.원하는 p_val 또는 기존 최소값이 현재 또는 계산된 다음 값보다 높을 경우 실제로 다음 값이 더 높아질 수 있습니다.
가장 좋은 것은 지정된 값으로 리셋하고 래퍼의 마지막에 "fix my sequences" 절차가 나타날 때까지 기다리는 것입니다.
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;
실제 데이터베이스에서는 이 메커니즘을 통해 리셋되는 다른 시퀀스가 100개 정도 존재하기 때문에 위의 절차에서는 Reset_Sequence_to_Data에 대한 콜이 97개 더 있습니다.
맘에 들어요?싫어?무관심?
다음 스크립트는 시퀀스를 원하는 값으로 설정합니다.
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;
/
이 저장 프로시저는 시퀀스를 재시작합니다.
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;
/
하는 다른 . 합니다.maxvalue ★★★★★★★★★★★★★★★★★」cycle언제요? when?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
stands 절차에서는 다른 세션이 값 0을 가져올 가능성이 있습니다.이 값은 문제가 될 수도 있고 문제가 되지 않을 수도 있습니다.이 경우 항상 다음을 수행할 수 있습니다.
- ★★
minvalue 1판에 - 번째 "는 합니다.
nextval - , 「」를 합니다.
nocycle속성을 다른 프로시저로 변환하여 나중에 실행할 수 있습니다(이 작업을 수행할 경우).
1) 다음과 같은 SEQUENCE를 작성했다고 가정합니다.
CREATE SEQUENCE TESTSEQ
INCREMENT BY 1
MINVALUE 1
MAXVALUE 500
NOCACHE
NOCYCLE
NOORDER
2) 이제 SEQUENCE에서 값을 가져옵니다.예를 들어, 아래와 같이 4회 페치를 실시했습니다.
SELECT TESTSEQ.NEXTVAL FROM dual
SELECT TESTSEQ.NEXTVAL FROM dual
SELECT TESTSEQ.NEXTVAL FROM dual
SELECT TESTSEQ.NEXTVAL FROM dual
3) 위의 4개의 명령을 실행하면 SEQUENCE 값이 4가 됩니다.이제 SEQUENCE 값을 다시 1로 리셋했다고 가정합니다.는, 다음의 순서에 따릅니다.다음과 같은 순서로 모든 단계를 수행합니다.
ALTER SEQUENCE TESTSEQ INCREMENT BY -3;SELECT TESTSEQ.NEXTVAL FROM dualALTER SEQUENCE TESTSEQ INCREMENT BY 1;SELECT TESTSEQ.NEXTVAL FROM dual
Jezus, 인덱스 재시작을 위한 이 모든 프로그램들은...내가 바보인지도 모르지만 오라클 12 이전 버전(재시작 기능이 있음)의 경우 심플의 문제점은 다음과 같습니다.
drop sequence blah;
create sequence blah
?
시퀀스의 INCREMENT 값을 변경하고 증가시킨 후 다시 변경하는 것은 매우 간단합니다.또, 시퀀스를 드롭/재작성했을 때와 같이, 모든 인가를 재정립할 필요는 없습니다.
다음과 같이 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. - 의 (, 현, 또, 또, 또, 또, 요, 요, 요, 요, 요, 요, third, third, third, third, third, third, third
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;
위의 코드는 시퀀스가 지연될 경우 작동합니다.
다음은 모든 자동 증분 시퀀스를 실제 데이터와 일치시키는 방법입니다.
이 스레드에서 이미 설명한 대로 다음 값을 적용하는 절차를 만듭니다.
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_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;
주의:
- 프로시저는 트리거 코드에서 이름을 추출하며 명명 규칙에 의존하지 않습니다.
- 실행 전에 생성된 코드를 확인하려면 마지막 두 줄의 주석을 바꿉니다.
사용자가 값을 알 필요가 없는 대체 방법을 만듭니다. 시스템이 변수를 가져와 업데이트 합니다.
--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;
저장 프로시저가 나에게 효과가 있었다.
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');
언급URL : https://stackoverflow.com/questions/51470/how-do-i-reset-a-sequence-in-oracle
'programing' 카테고리의 다른 글
| 토큰 기반 인증을 위한 JWT vs 쿠키 (0) | 2023.02.28 |
|---|---|
| ORA-12519가 간헐적으로 발생하는 원인(TNS: 적절한 핸들러를 찾을 수 없음) (0) | 2023.02.28 |
| 찾을 수 없는 아이콘 리액션-대박 (0) | 2023.02.23 |
| 워드프레스의 각 카테고리에서 하나의 게시물만 받는 방법 (0) | 2023.02.23 |
| 일치 문제를 사용한 regex에서의 Yup 유효성 검사 (0) | 2023.02.23 |