13

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);
Justin Cave
  • 227,342
  • 24
  • 367
  • 384
ol' forty guts
  • 343
  • 1
  • 2
  • 13
  • I don't have any sequences. I need to alter my identity column. – ol' forty guts Sep 15 '15 at 11:26
  • I've un-duplicated your question (and removed the MySQL tag) since I believe it is meaningfully different than the existing question. Feel free to add your solution as the answer (rather than just editing it into the question). – Justin Cave Sep 17 '15 at 21:27

3 Answers3

17

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);
ol' forty guts
  • 343
  • 1
  • 2
  • 13
5

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.

Sentry
  • 4,102
  • 2
  • 30
  • 38
-4

The following statement creates the sequence customers_seq in the sample schema oe. This sequence could be used to provide customer ID numbers when rows are added to the customers table.

CREATE SEQUENCE customers_seq START WITH 1000 INCREMENT BY 1 NOCACHE NOCYCLE;

The first reference to customers_seq.nextval returns 1000. The second returns 1001. Each subsequent reference will return a value 1 greater than the previous reference.

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

Sahin Yanlık
  • 1,171
  • 2
  • 11
  • 21