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

Question

Trying to create a sequence in Oracle that starts with the max value from a specific table. Why does this not work?

CREATE SEQUENCE transaction_sequence
  MINVALUE 0
  START WITH (SELECT MAX(trans_seq_no)
     FROM TRANSACTION_LOG) 
  INCREMENT BY 1
  CACHE 20;

Answer 1

If you can use PL/SQL, try (EDIT: Incorporates Neil's xlnt suggestion to start at next higher value):

SELECT 'CREATE SEQUENCE transaction_sequence MINVALUE 0 START WITH '||MAX(trans_seq_no)+1||' INCREMENT BY 1 CACHE 20'
  INTO v_sql
  FROM transaction_log;

EXECUTE IMMEDIATE v_sql;

Another point to consider: By setting the CACHE parameter to 20, you run the risk of losing up to 19 values in your sequence if the database goes down. CACHEd values are lost on database restarts. Unless you're hitting the sequence very often, or, you don't care that much about gaps, I'd set it to 1.

One final nit: the values you specified for CACHE and INCREMENT BY are the defaults. You can leave them off and get the same result.

Answer 2

Here I have my example which works just fine:

declare
 ex number;
begin
  select MAX(MAX_FK_ID)  + 1 into ex from TABLE;
  If ex > 0 then
    begin
            execute immediate 'DROP SEQUENCE SQ_NAME';
      exception when others then
        null;
    end;
    execute immediate 'CREATE SEQUENCE SQ_NAME INCREMENT BY 1 START WITH ' || ex || ' NOCYCLE CACHE 20 NOORDER';
  end if;
end;

Answer 3

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.