How to correct an identity column sequence after import

Thanks to the authors behind Grassroots-oracle for sharing this trick.

I finished an import of a table with an identity column, and for some reason, the sequence started to hand out number at the bottom of the range, although it had reached 10632546 in the source table.

My table's DDL:

CREATE TABLE MYTABLE
(
  MYID                   NUMBER GENERATED ALWAYS AS IDENTITY ( START WITH 21 MAXVALUE 9999999999999999999999999999 MINVALUE 1 NOCYCLE CACHE 20 NOORDER NOKEEP NOSCALE) NOT NULL,
  EVENT_TYPE             VARCHAR2(75 BYTE)      NOT NULL,
  SOURCE                 VARCHAR2(50 BYTE)      NOT NULL,
  FORMAT                 VARCHAR2(75 BYTE)      NOT NULL,
  EVKEY                  VARCHAR2(60 BYTE)      NOT NULL,
  TITLE                  VARCHAR2(40 BYTE)      NOT NULL,
  TIMESTAMP              TIMESTAMP(6)           DEFAULT current_timestamp,
  DOCUMENT               CLOB                   NOT NULL,
  FILEREF                VARCHAR2(200 BYTE)     DEFAULT 'some text' NOT NULL,
  TRANNUM                NUMBER
)
LOB (DOCUMENT) STORE AS SECUREFILE (
  TABLESPACE  LOB_DATA
)
TABLESPACE DATA;

After import, the developers tested an insert and the row was inserted with MYID set to 1.

There is no Primary Key on the table, which I pointed out to the developers.

The sequence name was ISEQ$$_88174, and the query below returned the number 2 after import in the destination database:

select "ISEQ$$_88174".nextval
from dual;

To "start where the sequence left off" in the source database, all I had to do was to execute:

ALTER TABLE MYTABLE
MODIFY (MYID  GENERATED ALWAYS AS IDENTITY ( START WITH LIMIT VALUE) );

If I queried the sequence's next value now, it had changed from 2 to 10632547.

I then checked the maximum value of the column:

select max(MYID) from MYTABLE;

And it returned 10632546, in other words, the next value would be the current max value + 1.

How to create a sequence and a trigger that will emulate "autonumber" in other RDBMS systems

If you have been working with other RDBMS systems, such as SQL Server from Microsoft, you may be used to a datatype called AUTONUMBER, which will automatically insert a freshly generated number and create a unique value in your table. Perfect for generation of primary key values.
In oracle, no such data type exist. To achieve the same functionality, you need to create two additional objects: a sequence and a trigger. The trigger will fire on certain events. The sequence will generate a new number. Here is a simple example on how to set it up.

CREATE TABLE ADDRESSES
(
 ADDRESS_ID NUMBER,
 EMLOYEE_ID NUMBER,
 STREET_ADDRESS VARCHAR2(40),
 STREET_ADDRESS2 VARCHAR2(40),
 CITY VARCHAR2 (30),
 STATE VARCHAR2(2),
 ZIP VARCHAR2(5),
 ZIP_PLUS VARCHAR2(4),
 COUNTRY VARCHAR2(20),
 CONSTRAINT PK_ADDRESSES PRIMARY KEY (ADDRESS_ID)-- "OUT-OF-LINE" CONSTRAINT CREATION
);

CREATE SEQUENCE ADDRESSES_SEQ
 START WITH 1
 INCREMENT BY 1
 MINVALUE 1
 MAXVALUE 1000000
 NOCACHE
 NOCYCLE
 NOORDER;

CREATE OR REPLACE TRIGGER ADDRESSES_TRG
BEFORE INSERT
ON ADDRESSES
FOR EACH ROW
WHEN (NEW.ADDRESS_ID IS NULL)
BEGIN
 SELECT ADDRESSES_SEQ.NEXTVAL
 INTO   :NEW.ADDRESS_ID
 FROM   DUAL;
END;
/