How to Create an Oracle Sequence Starting with Max Value from a Table

How to create an Oracle sequence starting with max value from a table?

you might want to start with max(trans_seq_no) + 1.

watch:

SQL> create table my_numbers(my_number number not null primary key);

Table created.

SQL> insert into my_numbers(select rownum from user_objects);

260 rows created.

SQL> select max(my_number) from my_numbers;

MAX(MY_NUMBER)
--------------
           260

SQL> create sequence my_number_sn start with 260;

Sequence created.

SQL> insert into my_numbers(my_number) values (my_number_sn.NEXTVAL);
insert into my_numbers(my_number) values (my_number_sn.NEXTVAL)
*
ERROR at line 1:
ORA-00001: unique constraint (NEIL.SYS_C00102439) violated

When you create a sequence with a number, you have to remember that the first time you select against the sequence, Oracle will return the initial value that you assigned it.

SQL> drop sequence my_number_sn;

Sequence dropped.

SQL> create sequence my_number_sn start with 261;

Sequence created.

SQL>  insert into my_numbers(my_number) values (my_number_sn.NEXTVAL);

1 row created.

If you're trying to do the 'gapless' thing, I strongly advise you to

1 not do it, and #2 not use a sequence for it.

Set starting sequence values as max(id)

You need dynamic sql.

DECLARE
   seq_id   INTEGER;
BEGIN
   SELECT MAX (id) INTO seq_id FROM test_table;

   EXECUTE IMMEDIATE
         'CREATE SEQUENCE seq_test 
                               START WITH '
      || seq_id
      || ' INCREMENT BY 1 MINVALUE 100000 MAXVALUE 9223372036854775807 NOCACHE';
END;
/

Create a sequence with maximum row value from a table - Oracle with Liquibase

I don't know Liquibase, but - as you tagged it with Oracle tag, then it just won't work that way.

Select MAX value first, then use it in CREATE SEQUENCE. You'll need dynamic SQL. Here's an example:

SQL> declare
  2    l_max number;
  3  begin
  4    select max(deptno) into l_max from dept;
  5
  6    execute immediate 'create sequence id_sequence start with ' || l_max;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> select id_sequence.nextval from dual;

   NEXTVAL
----------
        40

SQL> select id_sequence.nextval from dual;

   NEXTVAL
----------
        41

SQL>

Create a Sequence with START WITH from Query

The START WITH CLAUSE accepts an integer. You can form the "Create sequence " statement dynamically and then execute it using execute immediate to achieve this.

declare
    l_new_seq INTEGER;
begin
   select max(id) + 1
   into   l_new_seq
   from   test_table;

    execute immediate 'Create sequence test_seq_2
                       start with ' || l_new_seq ||
                       ' increment by 1';
end;
/

Check out these links.

http://download.oracle.com/docs/cd/B14117_01/server.101/b10759/statements_6014.htm

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/executeimmediate_statement.htm

Oracle Sequence or Select MAX() In an Auto Incrementing field?

You probably should use a sequence because they are so much faster than selecting from the table.

The problem with select max() from a table is that if you have more than one session at a time doing this they could both get the same max value and then add one to it and both use the same supposedly unique value. I can't really think of a reason that you would want to do select max() to get the next value.

Sequences are fast and you are guaranteed not to use the same value in multiple sessions.

Bobby

Oracle sequences - what happens if a sequence generates a number that already exists in the table

It should create error. Your insertion will fail for sequence 151 and with next try the sequence will be 152 and that will succeed.

You can create a new sequence starting from the higher value of all the id in current table then select from that sequence.
Create sequence starting from 152 is:

CREATE SEQUENCE New_seq
  MINVALUE 1
  START WITH 152
  INCREMENT BY 1
  CACHE 20;

And surely you can alter your sequence to restart it from 152 as @jarlh suggested.

ALTER SEQUENCE sequencename MINVALUE 152 RESTART

---

Related Topics