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

sql oracle sequence
Amar Premsaran Patel picture Amar Premsaran Patel · Apr 28, 2009 · Viewed 210.3k times · Source

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

DCookie picture DCookie · Apr 28, 2009

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.

Related questions

How do I reset a sequence in Oracle?

In PostgreSQL, I can do something like this: ALTER SEQUENCE serial RESTART WITH 0; Is there an Oracle equivalent?

Read More
How to retrieve the current value of an oracle sequence without increment it?

Is there an SQL instruction to retrieve the value of a sequence that does not increment it. Thanks. EDIT AND CONCLUSION As stated by Justin Cave It's not useful to try to "save" sequence number so select a_seq.nextval …

Read More
Oracle SQL: Use sequence in insert with Select Statement

Basically I want to run the following query: INSERT INTO historical_car_stats (historical_car_stats_id, year, month, make, model, region, avg_msrp, count) SELECT my_seq.nextval, '2010', '12', 'ALL', 'ALL', region, sum(avg_msrp * count) / …

Read More