Our aim is to improve and educate people...not spoon feed them with cut/paste solutions
________ .==.
[________>c((_ )
'=='
Sigh....
Anyway, here you go... feel free to cut/paste this - but of course, one day you'll probably cut/paste something the drops your database :-)
SQL> create table t ( x int primary key, y int, z int );
Table created.
SQL> create sequence seq_t;
Sequence created.
SQL>
SQL> insert into t select rownum, rownum, rownum from dual connect by level <= 30;
30 rows created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> set serverout on
SQL> declare
2 maxval int;
3 seqval int;
4 begin
5 for i in ( select ucc.column_name, s.sequence_name, uc.table_name
6 from user_cons_columns ucc,
7 user_constraints uc,
8 user_sequences s
9 where uc.constraint_name = ucc.constraint_name
10 and uc.constraint_type = 'P'
11 and ucc.position = 1
12 and s.sequence_name = 'SEQ_'||uc.table_name
13 )
14 loop
15 execute immediate 'select max('||i.column_name||') from '||i.table_name into maxval;
16 execute immediate 'select '||i.sequence_name||'.nextval from dual' into seqval;
17
18 dbms_output.put_line(maxval||','||seqval);
19
20 if maxval > seqval then
21 execute immediate 'alter sequence '||i.sequence_name||' increment by '|| ( maxval - seqval );
22 execute immediate 'select '||i.sequence_name||'.nextval from dual' into seqval;
23 execute immediate 'alter sequence '||i.sequence_name||' increment by 1';
24 execute immediate 'select '||i.sequence_name||'.nextval from dual' into seqval;
25 dbms_output.put_line(maxval||','||seqval);
26 end if;
27 end loop;
28 end;
29 /
30,1
30,31
PL/SQL procedure successfully completed.
The assumes that the sequence for table "A" will be called "SEQ_A". There is no *link* between sequences and tables.