Skip to Main Content
  • Questions
  • Script for increase sequence by table max pk value

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Pauline.

Asked: October 27, 2016 - 1:56 am UTC

Last updated: October 28, 2016 - 2:01 am UTC

Version: oracle 11.2.0.4

Viewed 10K+ times! This question is

You Asked

We have sequences created by table name format as
seq_<table_name>. Somehow sequence is lower than pl value and need to fix
by increasing sequence. Here I just simulate the situation as example below:

</>
ORACLE@hist > create table t1 (a_id number,t_nm varchar2(5));

Table created.

ORACLE@hist > create table t2(ck_id number,ck_txt varchar2(5));

Table created.

ORACLE@hist > create table t3(res_id number,res_txt varchar2(5));

Table created.

ORACLE@hist > create sequence seq_t1;

Sequence created.

ORACLE@hist > create sequence seq_t2;

Sequence created.

ORACLE@hist > create sequence seq_t3;

Sequence created.

ORACLE@hist > alter table t1 add constraint t1_pk primary key (a_id);

Table altered.

ORACLE@hist > alter table t2 add constraint t2_pk primary key (ck_id);

Table altered.

ORACLE@hist > alter table t3 add constraint t3_pk primary key (res_id);

Table altered.

ORACLE@hist > insert into t1 values (1, 't11');

1 row created.

ORACLE@hist > insert into t1 values (2,'t12');

1 row created.

ORACLE@hist > insert into t2 values (1, 't21');

1 row created.

ORACLE@hist > insert into t2 values (2, 't22');

1 row created.

ORACLE@hist > insert into t3 values (1, 't31');

1 row created.

ORACLE@hist > insert into t3 values (2,'t32');

1 row created.

ORACLE@hist > commit;

Commit complete.

ORACLE@hist > insert into t1 values (seq_t1.nextval,'t13');
insert into t1 values (seq_t1.nextval,'t13')
*
ERROR at line 1:
ORA-00001: unique constraint (ORACLE.T1_PK) violated

ORACLE@hist > insert into t2 values (seq_t2.nextval,'t23');
insert into t2 values (seq_t2.nextval,'t23')
*
ERROR at line 1:
ORA-00001: unique constraint (ORACLE.T2_PK) violated
</>

ORACLE@hist > insert into t3 values (seq_t3.nextval,'t33');
insert into t3 values (seq_t3.nextval,'t33')e
*
ERROR at line 1:
ORA-00001: unique constraint (ORACLE.T3_PK) violated

</>

Now I need to generate a script which can look up the number for max(pk_col) in table and increase the sequence number
bigger than max(pk_col). If max pk column (column_id =1) has the number with 290, then must increase sequence more than 290. Please help.

Thanks in advance.

and Connor said...

Just run the following:

select max(pk) y from table;
select seq.nextval x from dual;

alter sequence seq increment by <y-x+1>;
select seq.nextval from dual;
alter sequence seq increment by 1;


If you've got dozens of tables with the same problem (which would strike me as extremely odd...) you can do the same above in some PLSQL and execute immediate

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, October 27, 2016 - 11:27 am UTC

This is the logic I did already by SQL. We encountered the issue caused by using AWS Data Migration Service tool and now have many sequences need to be fixed. So I need to have PLSQL script to implement that logic for all these sequences. Our PK
is only 1 column and in table column ID 1. This is urgent task.
Please help by PlSQL script.

Thank You again

A reader, October 27, 2016 - 1:49 pm UTC

Thanks very much. I did these steps by SQL. But I have many sequences need to be fixed. The issue was caused by
incorrectly run AWS DMS tool to sync data between source and target DB. Now target DB max PK_id are higher than
seq.nextval. Could you provide the PLSQL code to help me? I need to implement it very soon.

Thank you again.
Chris Saxon
October 27, 2016 - 1:58 pm UTC

Loop through the sequences, dynamically generating the DDL Connor gave you for each one

declare
  stmt  varchar2(1000);
  mx_id int;
  nv    int;
begin
  for seqs in (
    select * from user_sequences
  ) loop
    -- code to dynamically map sequences to tables
    
    stmt := 'alter sequence ' || seqs.sequence_name || ' increment by ' || ((mx_id - nv) + 1);
    execute immediate stmt;
    select seq.nextval from dual; 
    stmt := 'alter sequence ' || seqs.sequence_name || ' increment by 1';
    execute immediate stmt;
  end loop;
end;
/


You need your own logic to determine which table each sequence belongs to!

A reader, October 27, 2016 - 2:38 pm UTC

Thanks for swift response. The challenge to me is to get max pk_id for each tables --pk_id in diff tables with diff column name. So if you may provide the code by taking my example in these 3 tables to get pk id in
stmt := 'alter sequence ' || seqs.sequence_name || ' increment by ' || ((mx_id - nv) + 1);

and also tell the code for dynamically map sequences to tables, then the code will be used right away. I am under the time constraints for it.

Thanks very much for your help

Connor McDonald
October 28, 2016 - 2:01 am UTC

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.

A reader, October 28, 2016 - 6:41 pm UTC

Thanks so much for providing the code to me. It is what exactly I need. From your code, I learned how to handle the SQL logic into PLSQL. It is definitely helps a lot for me. Especially for today's implementation of increasing so many
sequences.