Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Question about Sequences
Ryan,
below is the bit of code I use after importing data (not through SQL
loader). Look at the assumptions first, as it is used in our specific
databases!
Hope this helps.
Raphael
Raphael Ploix
Principal Software Engineer
raphael.ploix_at_threex.co.uk
Three X Communication Ltd
www.threex.co.uk
assumptions:
- REC_ID is the primary key of each table (surrogate key) - all sequence names are on the template: tableName_SQ - the procedure can resync the sequence for a single table or all tables inthe schema (null parameter)
<package>
procedure resyncSequence(
p_tablename IN varchar2 default NULL); </package>
<package body>
type t_dynsqlcur is ref cursor;
function make_seq_name(
p_tablename IN varchar2) return varchar2
return p_tablename || '_SQ';
end make_seq_name;
procedure trace (
p_text IN varchar2)
is
pragma autonomous_transaction;
begin
dbms_output.put_line(p_text); commit;
procedure resyncSequence(
p_tablename IN varchar2 default NULL) as
cv t_dynsqlcur; v_maxrid integer; v_seqval integer; v_minseq integer; v_seqname varchar2(30); cursor c_user_tables (cp_tablename varchar2) is select t.table_name from user_tables t, user_sequences s, user_tab_columns c where s.sequence_name = make_seq_name(t.table_name) and (cp_tablename is null or t.table_name = cp_tablename) and t.table_name = c.table_name and c.column_name = 'REC_ID'; begin dbms_application_info.set_module('<package>.resyncSequence',null); for cv_usertable in c_user_tables(p_tablename) loop v_seqname := make_seq_name(cv_usertable.table_name); begin execute immediate 'alter sequence ' || v_seqname || 'minvalue 0 increment by 1';
open cv for 'select ' || v_seqname || '.nextval from dual'; fetch cv into v_seqval; close cv; open cv for 'select max(rec_id) from ' || cv_usertable.table_name; fetch cv into v_maxrid; close cv; if v_maxrid is null then v_maxrid := 0; end if; exception when others then if cv%ISOPEN then close cv; end if; raise; end; if v_maxrid <> v_seqval then execute immediate 'alter sequence ' || v_seqname || ' minvalue 0 increment by ' || to_char(v_maxrid - v_seqval); begin open cv for 'select ' || v_seqname || '.nextval from dual'; fetch cv into v_seqval; trace('update sequence: ' || v_seqname || ' to new value: ' || v_seqval); close cv; exception when others then if cv%ISOPEN then close cv; end if; raise; end; end if; execute immediate 'alter sequence ' || v_seqname || ' minvalue 0 increment by 1'; end loop; dbms_application_info.set_module(null,null); exception when others then --pkgerr.LogError; raise;
</package body>
"Ryan Gaffuri" <rgaffuri_at_cox.net> wrote in message
news:1efdad5b.0206050710.4af26dd4_at_posting.google.com...
> Someone on my project recently truncated all the tables in one of our
> databases and imported new data. The problem is that the our sequence
> generators did not reset to match the highest sequence we have in our
> primary keys, so we keep hitting a unique constraint.
>
> Is there a command to reset sequences to the highest value in the
> table?
> Id prefer to run this dynamically than have to find the highest value
> for each sequence add set it to n + 1. This would be time consuming.
>
> Thanks.
Received on Thu Jun 06 2002 - 05:17:31 CDT