The sequences that need to reset the start number to 0 at the first day of new year.

create sequence: CREATE SEQUENCE SEQ_APP_NUMBER INCREMENT BY 1 START WITH 1 MINVALUE 0 MAXVALUE 99999 NOCYCLE NOCACHE ORDER;

set_sequence.sql

DECLARE
 Handle Integer;
 SqlText varchar2(2000);
 NewNextVal Integer;
 NewCurrVal Integer;
  
 CURSOR cursor_seq_names IS
  SELECT sequence_name,(last_number * (-1) + increment_by + min_value) last_number,increment_by FROM user_sequences
  WHERE upper(sequence_name) in ('TEST1','TEST2');
BEGIN
 FOR seq_name IN cursor_seq_names LOOP
  SqlText := 'Alter Sequence ' || seq_name.sequence_name || ' increment by ' || seq_name.last_number;
  dbms_output.put_line(SqlText);
  EXECUTE IMMEDIATE SqlText;
  SqlText := 'SELECT ' || seq_name.sequence_name || '.nextval FROM dual';
  EXECUTE IMMEDIATE SqlText INTO NewCurrVal;
  dbms_output.put_line(SqlText || ' --------> ' || NewCurrVal);
  SqlText := 'Alter Sequence ' || seq_name.sequence_name || ' increment by ' || seq_name.increment_by;
  dbms_output.put_line(SqlText);
  EXECUTE IMMEDIATE SqlText;
 END LOOP;
End;
/

declare
 Handle Integer;
 SqlText varchar2(2000);
 NewHighval Integer;
 Workint Integer;
Begin
 select max(job_id) into NewHighVal from job_posting;
 select job_posting_seq.nextval into workint from dual;
 If NewHighVal > WorkInt
 then
  WorkInt := NewHighVal - WorkInt;
 Else
  WorkInt := (WorkInt - NewHighVal) * -1;
 End if;
 SqlText := 'Alter Sequence job_posting_seq increment by ' || to_char(Workint);
 Handle := dbms_sql.open_cursor;
 dbms_sql.parse(handle, SqlText, DBMS_SQL.NATIVE);
 select job_posting_seq.nextval into workint from dual;
 SqlText := 'Alter Sequence job_posting_seq increment by 1' ;
 dbms_sql.parse(handle, SqlText,DBMS_SQL.NATIVE);
End;

Shell Scirpts:

#!/bin/sh
TSTAMP=`date +%y%m%d%H%M%S`
SHELLDIR=/data/oracle/jinn/resetsequence
ORATAB=/var/opt/oracle/oratab
ORACLE_HOME=`grep -v "^#" $ORATAB | grep -v "^$" | head -1 | cut -d: -f2`
export ORACLE_HOME
DBALIST="xbjin@ncs.com.sg"

$ORACLE_HOME/bin/sqlplus -s psmdba2/password@sitdb2.world <--set feedback off
--set heading off
set serveroutput on
set scan off
set linesize 1000
set pagesize 9999
set sqlblankline on
set trimspool on
spool $SHELLDIR/reset_sequence.txt
@$SHELLDIR/reset_sequence.sql
spool off
EOF

mailx -s "AutoExec SQLScripts List on - `date +%y%m%d`" $DBALIST -r admin@solaris.com < $SHELLDIR/$ORACLE_SID/$SQLFILE_DAILY

[@more@]