Deutsche Version
Setting a higher value for an already created and used sequence is simple: create loop which calls the sequences NEXTVAL method in a cursor until you reach the needed value. By default strategies it’s not that easy resetting a sequences current value to a lower value.
Of course you can drop and recreate a sequence but this will lead you to some problems like procedures become invalid and priviledge grants can be easily lost. For this reason I thought about resetting a sequences value through altering.
The overall procedure is quite simple:
1. Store all current properties of the sequence
2. Alter the sequence to cycle with the maximum value being the current and the minimum value being the desired, new value.
3. Let the sequnce cycle ;) In fact, fetch the sequnces NEXTVAL.
4. Reset the sequences properties to the original ones stored in step 1
All these steps are covered in this PL/SQL procedure:
Procedure reset_sequence
(p_schema varchar2,
p_seqname varchar2,
p_newvalue number) IS
cid INTEGER;
sequence_reset_exception EXCEPTION;
cursor c_wert is
SELECT
SEQUENCE_OWNER,
SEQUENCE_NAME,
MIN_VALUE,
MAX_VALUE,
INCREMENT_BY,
CYCLE_FLAG,
ORDER_FLAG,
CACHE_SIZE,
LAST_NUMBER
FROM SYS.ALL_SEQUENCES
WHERE sequence_owner = upper(p_schema)
and sequence_name = upper(p_seqname);
r_wert c_wert%rowtype;
v_wert number;
v_cycle varchar2(100);
BEGIN
open c_wert;
fetch c_wert into r_wert;
close c_wert;
--Aktuellen Wert der Sequence laden
cid := dbms_sql.open_cursor;
dbms_sql.parse(cid, 'select '||p_schema||'.'||p_seqname||'.nextval VALUE from dual' , dbms_sql.v7);
dbms_sql.define_column(cid, 1, v_wert);
if dbms_sql.execute_and_fetch(cid)<>1 then dbms_sql.close_cursor(cid); raise sequence_reset_exception; end if;
dbms_sql.column_value(cid, 1, v_wert);
dbms_sql.close_cursor(cid);
-- Sequence in einen Status setzen, indem Sie bearbeitet werden kann
cid := dbms_sql.open_cursor;
dbms_sql.parse(cid, 'ALTER SEQUENCE '||p_schema||'.'||p_seqname||' INCREMENT BY 1 MINVALUE '||to_char(p_newvalue - 1)||
' MAXVALUE '||v_wert||' CYCLE ORDER NOCACHE', dbms_sql.v7);
if dbms_sql.execute(cid)<>0 then dbms_sql.close_cursor(cid); raise sequence_reset_exception; end if;
dbms_sql.close_cursor(cid);
--Sequence einen "hochzahlen", bewirkt neuanfang
cid := dbms_sql.open_cursor;
dbms_sql.parse(cid, 'select '||p_schema||'.'||p_seqname||'.nextval VALUE from dual' , dbms_sql.v7);
if dbms_sql.execute_and_fetch(cid)<>1 then dbms_sql.close_cursor(cid); raise sequence_reset_exception; end if;
dbms_sql.close_cursor(cid);
-- Sequence in alten Status setzen
if p_newvalue -1 < r_wert.min_value then
r_wert.min_value := p_newvalue -1 ;
end if;
if r_wert.cycle_flag = 'Y' then
v_cycle := ' CYCLE ';
else
v_cycle := ' NOCYCLE ';
end if;
if r_wert.order_flag = 'Y' then
v_cycle := v_cycle||' ORDER ';
else
v_cycle := v_cycle||' NOORDER ';
end if;
cid := dbms_sql.open_cursor;
dbms_sql.parse(cid, 'ALTER SEQUENCE '||p_schema||'.'||p_seqname||
' INCREMENT BY '||TO_CHAR(r_wert.INCREMENT_BY) ||
' MINVALUE '||to_char(r_wert.min_value)||
' MAXVALUE '||to_char(r_wert.max_value)||
v_cycle || ' CACHE '||to_char(r_wert.cache_size), dbms_sql.v7);
if dbms_sql.execute(cid)<>0 then dbms_sql.close_cursor(cid); raise sequence_reset_exception; end if;
dbms_sql.close_cursor(cid);
EXCEPTION WHEN OTHERS THEN
dbms_sql.close_cursor(cid);
raise;
END;
For sure, the procedure is not state of the art, but it works… As you see, it uses the DBMS_SQL-package with v7 compatibility for the dynamic ALTER calls. Though it also works on old systems. Namely I created the procedure on an Oracle 7.3.4 instance.
Feel free to use it but I won’t take any responsibility for corruption.
Some limitations:
- the procedure doesn’t care about ORACLE-privs, so be careful with sharing an execute-priv.
- I’m not sure if it reconstructs all properties correctly, so it’s up to you to check.
- It’s not yet possible, to set a sequence to a higher value.