How do I reset a sequence in Oracle?
Here is a good procedure for resetting any sequence to 0 from Oracle guru Tom Kyte. Great discussion on the pros and cons in the links below too.
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;
/
From this page: Dynamic SQL to reset sequence value
Another good discussion is also here: How to reset sequences?
How can I reset all sequences in my Oracle DB to value 0?
You don't need dynamic SQL.
begin
for i in (select sequence_name from dba_sequences where sequence_name like '%SEQ_PR%') LOOP
reset_seq(i.sequence_name); --> this is just fine
end loop;
end;
/
Example (I'm using USER_SEQUENCES instead):
SQL> select seq_pr1.nextval from dual;
NEXTVAL
----------
9
SQL> select seq_pr2.nextval from dual;
NEXTVAL
----------
8
SQL> begin
2 for i in (select sequence_name from user_sequences where sequence_name like '%SEQ_PR%') LOOP
3 reset_seq(i.sequence_name);
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> select seq_pr1.nextval from dual;
NEXTVAL
----------
1
SQL> select seq_pr2.nextval from dual;
NEXTVAL
----------
1
SQL>
How do I reset a sequence in Oracle APEX or fill my PK automatically without sequence and trigger, starting from number 1 every time i delete my data?
You say i can not reset this sequence but you don't say why so I'm assuming that you got an error. It is not possible to execute ddl statements in pl/sql directly, but it can be done using EXECUTE IMMEDIATE.
CREATE SEQUENCE koen_s START WITH 1;
Sequence KOEN_S created.
SELECT koen_s.NEXTVAL FROM DUAL;
NEXTVAL
----------
1
BEGIN
EXECUTE IMMEDIATE 'ALTER SEQUENCE koen_s RESTART START WITH 1';
END;
/
PL/SQL procedure successfully completed.
SELECT koen_s.NEXTVAL FROM DUAL;
NEXTVAL
----------
1
Reset Oracle Sequence to have MIN VALUE = 1 and STARTING number from 1
Is it possible to set the min value = 1, and next val to be 1, after the reset is done?
You could do it in two steps:
- increment_by value one less than the current value of the sequence.
- reset increment_by back to 1.
The logic is that, you shouldn't decrement the sequence back to zero, since the minvalue you want is 1, so, the nextval cannot be less than the minval.
For example,
SQL> CREATE SEQUENCE s START WITH 20 MINVALUE 0 INCREMENT BY 1;
Sequence created.
SQL> SELECT s.nextval FROM dual;
NEXTVAL
----------
20
SQL> ALTER SEQUENCE s INCREMENT BY -19 MINVALUE 1;
Sequence altered.
SQL> SELECT s.nextval FROM dual;
NEXTVAL
----------
1
SQL> ALTER SEQUENCE s INCREMENT BY 1 MINVALUE 1;
Sequence altered.
SQL> SELECT s.nextval FROM dual;
NEXTVAL
----------
2
SQL> SELECT min_value, increment_by FROM user_sequences WHERE sequence_name='S';
MIN_VALUE INCREMENT_BY
---------- ------------
1 1
So, the min_value and increment_by is now reset to 1 respectively. The nextvalue could be 1 only once before you reset the increment_by to 1 again.
So, I don't see any practical use of what you want to achieve. However, it could be done as demonstrated above.
To implement the above logic in your procedure, do as following:
Setup
SQL> DROP SEQUENCE S;
Sequence dropped.
SQL> CREATE SEQUENCE s START WITH 20 MINVALUE 0 INCREMENT BY 1;
Sequence created.
SQL> SELECT s.nextval FROM dual;
NEXTVAL
----------
20
Modify your procedure as:
SQL> CREATE OR REPLACE PROCEDURE reset_seq(
2 p_seq_name IN VARCHAR2 )
3 IS
4 l_val NUMBER;
5 BEGIN
6 EXECUTE IMMEDIATE 'select ' || p_seq_name || '.nextval from dual' INTO l_val;
7 l_val := l_val - 1;
8 dbms_output.put_line('l_val = '||l_val);
9 EXECUTE IMMEDIATE 'alter sequence ' ||
10 p_seq_name || ' increment by -' || l_val || ' minvalue 1';
11 EXECUTE IMMEDIATE 'select ' || p_seq_name || '.nextval from dual' INTO l_val;
12 dbms_output.put_line('1st Nextval is '||l_val);
13 EXECUTE IMMEDIATE 'alter sequence ' || p_seq_name ||
14 ' increment by 1 MINVALUE 1';
15 EXECUTE IMMEDIATE 'select ' || p_seq_name || '.nextval from dual' INTO l_val;
16 dbms_output.put_line('2nd Nextval is '||l_val);
17 END;
18 /
Procedure created.
SQL> SET serveroutput ON
SQL> EXEC reset_seq('S');
l_val = 20
1st Nextval is 1
2nd Nextval is 2
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT min_value, increment_by FROM user_sequences where sequence_name='S';
MIN_VALUE INCREMENT_BY
---------- ------------
1 1
As I said, I don't see any practical use of it. Your nextval is practically usable only from 2. When it is 1, you need to do an ALTER SEQUENCE once again to reset the increment_by back to 1.
Best way to reset an Oracle sequence to the next value in an existing column?
These two procedures let me reset the sequence and reset the sequence based on data in a table (apologies for the coding conventions used by this client):
CREATE OR REPLACE PROCEDURE SET_SEQ_TO(p_name IN VARCHAR2, p_val IN NUMBER)
AS
l_num NUMBER;
BEGIN
EXECUTE IMMEDIATE 'select ' || p_name || '.nextval from dual' INTO l_num;
-- Added check for 0 to avoid "ORA-04002: INCREMENT must be a non-zero integer"
IF (p_val - l_num - 1) != 0
THEN
EXECUTE IMMEDIATE 'alter sequence ' || p_name || ' increment by ' || (p_val - l_num - 1) || ' minvalue 0';
END IF;
EXECUTE IMMEDIATE 'select ' || p_name || '.nextval from dual' INTO l_num;
EXECUTE IMMEDIATE 'alter sequence ' || p_name || ' increment by 1 ';
DBMS_OUTPUT.put_line('Sequence ' || p_name || ' is now at ' || p_val);
END;
CREATE OR REPLACE PROCEDURE SET_SEQ_TO_DATA(seq_name IN VARCHAR2, table_name IN VARCHAR2, col_name IN VARCHAR2)
AS
nextnum NUMBER;
BEGIN
EXECUTE IMMEDIATE 'SELECT MAX(' || col_name || ') + 1 AS n FROM ' || table_name INTO nextnum;
SET_SEQ_TO(seq_name, nextnum);
END;
How to Reset Oracle Sequence Safely?
The issue is that both your job-submitted procedure and your external program could both call nextval between steps 2 and 4; and could call in either order; and potentially the external program could make multiple calls.
You could try to mitigate that by changing both the procedure and the external call.
At the moment if step 3 errors the procedure terminates and step 4 is never reached, so all future calls to nextval will continue to error. So you could trap and ignore the ORA-08004 error, on the basis that you'll only get that if there has been an external call between steps 2 and 3, the sequence has therefore been incremented back to zero by that call and thus step 3 is effectively redundant in that scenario:
create or replace procedure reset_seq(p_seq_name in varchar2) is
l_val number;
e_8004 exception;
pragma exception_init(e_8004, -8004);
begin
execute immediate 'select ' || p_seq_name || '.nextval from dual'
INTO l_val; --1
execute immediate 'alter sequence ' || p_seq_name || ' increment by -' ||
l_val || ' minvalue 0'; --2
begin
execute immediate 'select ' || p_seq_name || '.nextval from dual'
INTO l_val; --3
exception
when e_8004 then
-- nextval has already been called by someone else
null;
end;
execute immediate 'alter sequence ' || p_seq_name ||
' increment by 1 minvalue 0'; --4
end;
Now if an external program calls nextval between steps 2 and 3 then you'll get the error at step 3 but ignore it, and step 4 will still happen.
Your external program will get a nextval value of zero if it makes its call between steps 2 and 3; and will get the ORA-08004 if it calls between steps 3 and 4 - or if it makes multiple calls in the window between steps 2 and 4. So, assuming you don't expect zero to be a valid result (which seems reasonable as you wouldn't normally ever get that), you can make repeated calls until you get a non-zero answer and no error. In pseudocode something like:
loop
val = seq.nextval;
if error == -8004 then continue;
if val == 0 then continue;
break;
end loop
You could consider putting that logic into a PL/SQL function and have your program call the function instead of accessing the sequence directly, both to hide that complexity and to avoid having to repeat it if you have more than one program potentially affected.
How can I reset a sequence in Oracle to value 1 once at the beginning of the year?
You can schedule a database job using Oracle Scheduler to do it
http://docs.oracle.com/cd/E11882_01/server.112/e25494/scheduse.htm#ADMIN034
Reset Oracle sequence which increments by 50
You should check for errors and print out the SQL statements you want to execute. Then you wouldn't have to both with Stack Overflow:
EXECUTE IMMEDIATE 'SELECT '||p_seq_name||'.NEXTVAL FROM DUAL' INTO l_val;
l_val := l_val - l_minvalue;
EXECUTE IMMEDIATE 'ALTER SEQUENCE '||p_seq_name||'INCREMENT BY -'||l_val||
'MINVALUE '||l_minvalue;
EXECUTE IMMEDIATE 'SELECT '||p_seq_name||'.NEXTVAL FROM DUAL' INTO l_val;
EXECUTE IMMEDIATE 'ALTER SEQUENCE '||p_seq_name||' INCREMENT BY 50 MINVALUE '
------------------------------------------------------^ space
||l_minvalue;
END;
For this reason, I prefer using replace() to construct such strings. For instance:
v_sql := 'ALTER SEQUENCE [p_seq_name] INCREMENT BY -[l_val] MINVALUE [l_minvalue]';
v_sql := replace(v_sql, '[p_seq_name]', p_seq_name);
v_sql := replace(v_sql, '[l_val]', l_val);
v_sql := replace(v_sql, '[l_minvalue]', l_minvalue);
Related Topics
How Does Database Indexing Work
How to Perform an If...Then in an SQL Select
Using an Alias Column in the Where Clause in Postgresql
How to Get Multiple Counts With One SQL Query
Group by Clause in MySQL and Postgresql, Why the Error in Postgresql
How to Use an Insert Statement'S Output Clause to Get the Identity Value
MySQL Trigger to Prevent Insert Under Certain Conditions
How to Make a Row Generator in MySQL
How to Concatenate Multiple MySQL Rows into One Field
Select Rows Which Are Not Present in Other Table
When Do I Need to Use a Semicolon VS a Slash in Oracle Sql
Index For Finding an Element in a Json Array
How to Update Only One Field Using Entity Framework
Add a Column With a Default Value to an Existing Table in SQL Server