1

DB

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit

Action

After a data migration - from an old table to a new table - we created a new sequence and executed this command to sync it's next value to be equal to table maximum id.

SQL

-- SYNC SEQUENCE WITH TABLE
SELECT level, {SEQUENCE_NAME}.NEXTVAL
FROM dual
CONNECT BY level <= (
 SELECT MAX(ID)
 FROM {TABLE}
);

Question

  1. Is there any side effect on calling this SQL - since the keyword CONNECT is used to make connections to DB ?
  2. Is there a more efficient way to do such task?

Useful Links

Oracle - CONNECT Keywork - Reference

2

CONNECT in this context has nothing to do with database connections.

hierarchical_query_clause ::=

enter image description here

You can change the increment of a sequence to set it to a required value. Example:

SQL> create sequence s1;

Sequence created.

SQL> select s1.nextval from dual;

   NEXTVAL
----------
         1

SQL> alter sequence s1 increment by 100;

Sequence altered.

SQL> select s1.nextval from dual;

   NEXTVAL
----------
       101

SQL> alter sequence s1 increment by 1;

Sequence altered.

SQL> select s1.nextval from dual;

   NEXTVAL
----------
       102

You can also use the undocumented RESTART clause, but that does not work in 11.2:

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Apr 30 12:44:26 2019
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> create sequence s1;

Sequence created.

SQL> select s1.nextval from dual;

   NEXTVAL
----------
         1

SQL> alter sequence s1 restart start with 101;

Sequence altered.

SQL> select s1.nextval from dual;

   NEXTVAL
----------
       101

SQL> select s1.nextval from dual;

   NEXTVAL
----------
       102

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.