oracle how to change the next autogenerated value of the identity column

Question

I've created table projects like so:

CREATE TABLE projects (
  project_id NUMBER(10,0) GENERATED BY DEFAULT ON NULL AS IDENTITY ,
  project_name VARCHAR2(75 CHAR) NOT NULL

Then I've inserted ~150,000 rows while importing data from my old MySQL table. the MySQL had existing id numbers which i need to preserve so I added the id number to the SQL during the insert. Now when I insert new rows into the oracle table, the id is a very low number. Can you tell me how to reset my counter on the project_id column to start at 150,001 so not to mess up any of my existing id numbers? essentially i need the oracle version of:

ALTER TABLE tbl AUTO_INCREMENT = 150001;

Edit: Oracle 12c now supports the identity data type, allowing an auto number primary key that does not require us to create a sequence + insert trigger.

SOLUTION: after some creative google search terms I was able to find this thread on the oracle docs site. here is the solution for changing the identity's nextval:

ALTER TABLE     projects    MODIFY  project_id  GENERATED BY DEFAULT ON NULL AS IDENTITY ( START WITH   150000);

Answer 1

Here is the solution that i found on this oracle thread:. The concept is to alter your identity column rather than adjust the sequence. Actually, the sequences that are automatically created aren't editable or drop-able.

ALTER TABLE     projects    MODIFY  project_id  GENERATED BY DEFAULT ON NULL AS IDENTITY ( START WITH   150000);

Answer 2

According to this source, you can do it like this:

ALTER TABLE projects MODIFY project_id
    GENERATED BY DEFAULT ON NULL AS IDENTITY (START WITH LIMIT VALUE);

The START WITH LIMIT VALUE clause can only be specified with an ALTER TABLE statement (and by implication against an existing identity column). When this clause is specified, the table will be scanned for the highest value in the PROJECT_ID column and the sequence will commence at this value + 1.

The same is also stated in the oracle thread referenced in OP's own answer:

START WITH LIMIT VALUE, which is specific to identity_options, can only be used with ALTER TABLE MODIFY. If you specify START WITH LIMIT VALUE, then Oracle Database locks the table and finds the maximum identity column value in the table (for increasing sequences) or the minimum identity column value (for decreasing sequences) and assigns the value as the sequence generator's high water mark. The next value returned by the sequence generator will be the high water mark + INCREMENT BY integer for increasing sequences, or the high water mark - INCREMENT BY integer for decreasing sequences.