Is there a way where I can set last_number field of sequence of each table in Oracle to the maximum value of the primary key of that table?

Question

Is there a way where I can set last_number field of sequence of each table in Oracle to the maximum value of the primary key of that table using stored proc?

There are some data mismatch between the current sequence and the max value of primary key, so I am trying to reset the sequence to match that max value of the primary key so it will increment correctly. I am looking to do it via stored proc, and there are many tables so its not feasible for me to manually edit them one by one. Any help would be greatly appreciated!

score 0 · Answer 1 · answered Jan 21 '20 at 07:42

I don't know what is your expected result from the query but you can use the following query to fetch the maximum number in the primary key column as follows:

SELECT
    C.TABLE_NAME,
    CC.COLUMN_NAME,
    to_number(xmlquery('/ROWSET/ROW/C/text()'
        passing xmltype(dbms_xmlgen.getxml(
          'select MAX( "' || CC.column_name || '") as c '
          || 'from "' || C.table_name || '"'))
    returning content)) as c
FROM
    USER_CONSTRAINTS C
    JOIN USER_CONS_COLUMNS CC ON C.CONSTRAINT_NAME = CC.CONSTRAINT_NAME
    JOIN USER_TAB_COLUMNS TC ON TC.COLUMN_NAME = CC.COLUMN_NAME
                                AND TC.TABLE_NAME = C.TABLE_NAME
WHERE
    C.CONSTRAINT_TYPE = 'P'
    AND CC.POSITION = 1
    AND TC.DATA_TYPE = 'NUMBER'
--AND C.TABLE_NAME IN ()
ORDER BY C DESC NULLS LAST;

Cheers!!

Barbaros Özhan · Accepted Answer · 2020-01-21T07:52:56.233

Assuming you have a table called tab :

SQL> create table tab ( id int primary key );

and populated that primary key(id) column upto 10 :

SQL> insert into tab select level from dual connect by level <= 10;

e.g. max value of id is 10 and you want to set your sequence (create if not exists yet) to 10 :

SQL> create or replace procedure pr_alter_seq is
  nmr tab.id%type;
  ext pls_integer := 0;
begin
  select max(id) into nmr from tab;
  begin
     select 1 into ext from user_sequences s where regexp_like(sequence_name,'myseq','i');
   exception when no_data_found then ext := 0;
  end;
  if ext = 0 then
     execute immediate 'create sequence myseq';
  end if;
     execute immediate 'alter sequence myseq increment by '||nmr;
     execute immediate 'select myseq.nextval from dual' into nmr;
     execute immediate 'alter sequence myseq increment by 1';
end;
/
SQL> exec pr_alter_seq;

Demo

Is there a way where I can set last_number field of sequence of each table in Oracle to the maximum value of the primary key of that table?

2 Answers2