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.
hi i have written a code below.
but the alter sequence is not being executed … but the return value is 0
kindly check
#!/bin/ksh
LOGFILE=/tmp/sequence.log
echo “Log file : $LOGFILE”
>${LOGFILE}
DBLOGIN=”dacscan”
DBPASSWD=”mnc”
host_name=`hostname`
function check_all_function
{
echo ” checking all sequences ” | tee -a ${LOGFILE}
echo “check_all_sequences::start” | tee -a ${LOGFILE}
#stat=sqlplus ${DBLOGIN}/${DBPASSWD} <${LOGFILE}
sqlplus -s dacscan/mnc <<!
CREATE OR REPLACE PROCEDURE exec
(STRING IN varchar2,
logfile IN utl_file.file_type
) AS
cursor_name number;
ret INTEGER;
BEGIN
cursor_name := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cursor_name, string, DBMS_SQL.native);
ret := DBMS_SQL.EXECUTE(cursor_name);
DBMS_SQL.CLOSE_CURSOR(cursor_name);
utl_file.put_line(logfile,’procedure exec ret value is ‘||ret);
EXCEPTION
when OTHERS then
utl_file.put_line(logfile,’Error occured in procedure exec ‘||SQLCODE||SQLERRM);
END;
/
show errors
create or replace procedure resetsequence
(seqname IN varchar2,
v_diff IN number,
logfile IN utl_file.file_type
)
as
sqlstm varchar2(200);
BEGIN
utl_file.put_line(logfile,’sequence name difference : ‘);
sqlstm := ‘alter sequence ‘||seqname||’ increment by ‘||v_diff;
utl_file.put_line(logfile,sqlstm);
exec(sqlstm, logfile);
commit;
sqlstm := ‘select ‘||seqname||’.nextval from dual’;
utl_file.put_line(logfile,sqlstm);
exec(sqlstm, logfile);
sqlstm := ‘alter sequence ‘||seqname||’ increment by 1′;
utl_file.put_line(logfile,sqlstm);
exec(sqlstm, logfile);
commit;
EXCEPTION
when OTHERS then
utl_file.put_line(logfile,’Error occured in procedure resetsequence ‘||SQLCODE||SQLERRM);
commit;
END;
/
show errors
create or replace procedure check_all_sequences
(
host_name IN varchar2
)as
logfile utl_file.file_type;
curr_val number(10);
next_val number(10);
diff number(10);
curr_val_tmp varchar2(20);
begin
logfile := utl_file.fopen(‘/tmp’,'sequence.log’,'W’);
UTL_FILE.PUT_LINE(logfile,’Start Processing ‘);
utl_file.put_line(logfile,’Processing WRKLOGKEY sequence for table worklog’);
select max(wrklogkey) into curr_val from worklog;
select wrklogkey.nextval into next_val from dual;
utl_file.put_line(logfile,’max in table :’||curr_val||’ nextval:’||next_val);
diff := curr_val – next_val;
utl_file.put_line(logfile,’difference:’||diff);
if(diff >= 0)
then
resetsequence(‘WRKLOGKEY’,diff,logfile);
end if;
utl_file.put_line(logfile,’Processing areakey sequence for table arearef ‘);
select max(areakey) into curr_val from arearef;
select areakey.nextval into next_val from dual;
utl_file.put_line(logfile,’max in table :’||curr_val||’ nextval:’||next_val);
diff := curr_val – next_val;
utl_file.put_line(logfile,’difference:’||diff);
if(diff >= 0)
then
resetsequence(‘AREAKEY’,diff,logfile);
end if;
utl_file.put_line(logfile,’Processing NM_SB_SNC_ID sequence for table SB_SNC_PORTS’);
select max(NM_SB_SNC_ID) into curr_val from SB_SNC_PORTS;
select NM_SB_SNC_ID.nextval into next_val from dual;
utl_file.put_line(logfile,’max in table :’||curr_val||’ nextval:’||next_val);
diff := curr_val – next_val;
utl_file.put_line(logfile,’difference:’||diff);
if(diff >= 0)
then
resetsequence(‘NM_SB_SNC_ID’,diff,logfile);
end if;
utl_file.put_line(logfile,’Processing SB_SNET_KEY sequence for table SB_EMS_SUBNET’);
select max(sb_snet_key) into curr_val from SB_EMS_SUBNET;
select sb_snet_key.nextval into next_val from dual;
utl_file.put_line(logfile,’max in table :’||curr_val||’ nextval:’||next_val);
diff := curr_val – next_val;
utl_file.put_line(logfile,’difference:’||diff);
if(diff >= 0)
then
resetsequence(‘sb_snet_key’,diff,logfile);
end if;
utl_file.put_line(logfile,’Processing SUBMAPKEY sequence for table SUBMAPREF’);
select max(SUBMAPKEY) into curr_val from SUBMAPREF;
select SUBMAPKEY.nextval into next_val from dual;
utl_file.put_line(logfile,’max in table :’||curr_val||’ nextval:’||next_val);
diff := curr_val – next_val;
utl_file.put_line(logfile,’difference:’||diff);
if(diff >= 0)
then
resetsequence(‘SUBMAPKEY’,diff,logfile);
end if;
utl_file.put_line(logfile,’Processing TRENCHCODE sequence for table TRENCH_INFO’);
select max(TRENCHCODE) into curr_val from TRENCH_INFO;
select TRENCHCODE.nextval into next_val from dual;
utl_file.put_line(logfile,’max in table :’||curr_val||’ nextval:’||next_val);
diff := curr_val – next_val;
utl_file.put_line(logfile,’difference:’||diff);
if(diff >= 0)
then
resetsequence(‘TRENCHCODE’,diff,logfile);
end if;
utl_file.put_line(logfile,’Processing WRKGRP_KEY sequence for table WORKGRPS’);
select max(WRKGRP_KEY) into curr_val from WORKGRPS;
select WRKGRP_KEY.nextval into next_val from dual;
utl_file.put_line(logfile,’max in table :’||curr_val||’ nextval:’||next_val);
diff := curr_val – next_val;
utl_file.put_line(logfile,’difference:’||diff);
if(diff >= 0)
then
resetsequence(‘WRKGRP_KEY’,diff,logfile);
end if;
– utl_file.put_line(logfile,’Processing AUTO_MERGE_ID sequence for table AUTO_MERGE_CIRCUIT’);
— select max(AUTO_MERGE_ID) into curr_val from AUTO_MERGE_CIRCUIT;
— select AUTO_MERGE_ID.nextval into next_val from dual;
–diff := curr_val – next_val;
–if(diff >= 0)
–then
– resetsequence(‘AUTO_MERGE_ID’,diff,logfile);
— end if;
utl_file.put_line(logfile,’Processing CACNUM sequence for table FACODR’);
select max(substr(CAC,2)) into curr_val_tmp from FACODR;
–curr_val := substr(curr_val_tmp,2);
select CACNUM.nextval into next_val from dual;
utl_file.put_line(logfile,’max in table :’||curr_val||’ nextval:’||next_val);
diff := curr_val – next_val;
utl_file.put_line(logfile,’difference:’||diff);
if(diff >= 0)
then
resetsequence(‘CACNUM’,diff,logfile);
end if;
utl_file.put_line(logfile,’Processing DL_CKT_REL_ID sequence for table DL_CKT_REL’);
select max(DL_CKT_REL_ID) into curr_val from DL_CKT_REL;
select DL_CKT_REL_ID.nextval into next_val from dual;
utl_file.put_line(logfile,’max in table :’||curr_val||’ nextval:’||next_val);
diff := curr_val – next_val;
utl_file.put_line(logfile,’difference:’||diff);
if(diff >= 0)
then
resetsequence(‘DL_CKT_REL_ID’,diff,logfile);
end if;
utl_file.put_line(logfile,’Processing FMLAYOUT sequence for table FACODR’);
select max(LAYOUT) into curr_val from FACODR;
select FMLAYOUT.nextval into next_val from dual;
utl_file.put_line(logfile,’max in table :’||curr_val||’ nextval:’||next_val);
diff := curr_val – next_val;
utl_file.put_line(logfile,’difference:’||diff);
if(diff >= 0)
then
resetsequence(‘FMLAYOUT’,diff,logfile);
end if;
utl_file.put_line(logfile,’Processing GRPKEY sequence for table FACODR’);
select nvl(max(GRPKEY),0) into curr_val from FACODR;
select GRPKEY.nextval into next_val from dual;
utl_file.put_line(logfile,’max in table :’||curr_val||’ nextval:’||next_val);
diff := curr_val – next_val;
utl_file.put_line(logfile,’difference:’||diff);
if(diff >= 0)
then
resetsequence(‘GRPKEY’,diff,logfile);
end if;
utl_file.put_line(logfile,’Processing CLONUM sequence for table FACODR’);
select max(substr(clo,4)) into curr_val from FACODR;
select ((CLONUM.nextval * 1000) + 1) into next_val from dual;
utl_file.put_line(logfile,’max in table :’||curr_val||’ nextval:’||next_val);
diff := curr_val – next_val;
utl_file.put_line(logfile,’difference:’||diff);
if(diff >= 0)
then
resetsequence(‘CLONUM’,diff,logfile);
end if;
utl_file.fclose(logfile);
END;
/
show errors
!echo “check_all_sequences::end”
execute check_all_sequences(‘$host_name’);
!
}
check_all_function
more specifically
#!/bin/ksh
LOGFILE=/tmp/sequence.log
echo “Log file : $LOGFILE”
>${LOGFILE}
DBLOGIN=”dacscan”
DBPASSWD=”mnc”
host_name=`hostname`
function check_all_function
{
echo ” checking all sequences ” | tee -a ${LOGFILE}
echo “check_all_sequences::start” | tee -a ${LOGFILE}
#stat=sqlplus ${DBLOGIN}/${DBPASSWD} <${LOGFILE}
sqlplus -s dacscan/mnc <<!
CREATE OR REPLACE PROCEDURE exec
(string1 IN varchar2,
logfile IN utl_file.file_type
) AS
cur_id INTEGER;
ret number;
BEGIN
cur_id := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cur_id,string1 , DBMS_SQL.native);
ret := DBMS_SQL.EXECUTE(cur_id);
DBMS_SQL.CLOSE_CURSOR(cur_id);
utl_file.put_line(logfile,’procedure exec ret value is ‘||ret);
return;
EXCEPTION
when OTHERS then
utl_file.put_line(logfile,’Error occured in procedure exec ‘||SQLCODE||SQLERRM);
END;
/
show errors
create or replace procedure resetsequence
(seqname IN varchar2,
v_diff IN number,
logfile IN utl_file.file_type
)
as
sqlstm varchar2(200);
BEGIN
utl_file.put_line(logfile,’sequence name difference : ‘);
sqlstm := ‘alter sequence ‘||seqname||’ increment by ‘||v_diff;
utl_file.put_line(logfile,sqlstm);
exec(sqlstm, logfile);
sqlstm := ‘select ‘||seqname||’.nextval from dual’;
utl_file.put_line(logfile,sqlstm);
exec(sqlstm, logfile);
sqlstm := ‘alter sequence ‘||seqname||’ increment by 1′;
utl_file.put_line(logfile,sqlstm);
exec(sqlstm, logfile);
EXCEPTION
when OTHERS then
utl_file.put_line(logfile,’Error occured in procedure resetsequence ‘||SQLCODE||SQLERRM);
END;
/
show errors
create or replace procedure check_all_sequences
(
host_name IN varchar2
)as
logfile utl_file.file_type;
curr_val number(10);
next_val number(10);
diff number(10);
curr_val_tmp varchar2(20);
begin
logfile := utl_file.fopen(‘/tmp’,'sequence.log’,'W’);
UTL_FILE.PUT_LINE(logfile,’Start Processing ‘);
utl_file.put_line(logfile,’Processing DL_CKT_REL_ID sequence for table DL_CKT_REL’);
select max(DL_CKT_REL_ID) into curr_val from DL_CKT_REL;
select DL_CKT_REL_ID.nextval into next_val from dual;
utl_file.put_line(logfile,’max in table :’||curr_val||’ nextval:’||next_val);
diff := curr_val – next_val;
utl_file.put_line(logfile,’difference:’||diff);
if(diff >= 0)
then
resetsequence(‘DL_CKT_REL_ID’,diff,logfile);
end if;
utl_file.fclose(logfile);
END;
/
show errors
!echo “check_all_sequences::end”
execute check_all_sequences(‘$host_name’);
!
}
check_all_function
—————————————————————-
log file:
Start Processing
Processing DL_CKT_REL_ID sequence for table DL_CKT_REL
max in table :22 nextval:15
difference:7
sequence name difference :
alter sequence DL_CKT_REL_ID increment by 7
procedure exec ret value is 0
select DL_CKT_REL_ID.nextval from dual
procedure exec ret value is 0
alter sequence DL_CKT_REL_ID increment by 1
procedure exec ret value is 0
———————————————————
log for the second time
Start Processing
Processing DL_CKT_REL_ID sequence for table DL_CKT_REL
max in table :22 nextval:16
difference:6
sequence name difference :
alter sequence DL_CKT_REL_ID increment by 6
procedure exec ret value is 0
select DL_CKT_REL_ID.nextval from dual
procedure exec ret value is 0
alter sequence DL_CKT_REL_ID increment by 1
procedure exec ret value is 0
———————————–
but the sequcence is not incrementing by the difference …..
pleaswe reply ASAP
Hi!
Possibly it’s because you don’t really fetch a row from your cursor while calling the seq.nextval. I’m not sure how this is handelt by the DBMS_SQL package.
Try to change
sqlstm := ’select ‘||seqname||’.nextval from dual’;
utl_file.put_line(logfile,sqlstm);
exec(sqlstm, logfile);
to something like:
cid := dbms_sql.open_cursor;
dbms_sql.parse(cid, ’select ‘||seqname||’.nextval from dual’, dbms_sql.v7);
if dbms_sql.execute_and_fetch(cid)1 then dbms_sql.close_cursor(cid); raise some_exception; end if;
dbms_sql.close_cursor(cid);
in your resetSequence procedure. I think the execute alone will just verify your SQL-code. If this also will not work, try to output the value, the seq.nextval returns to see if it increments by 1 or by n. Another idea I have is to turn off the caching, if it increments by 1 it possibly comes from cache and you first have to empty the sequences cache.
Hope it helps,
Alex