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
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/205087/viewspace-786068/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/205087/viewspace-786068/