Sequence

Sequence

A sequence is an object in Oracle that is used to generate a number sequence. This can be useful when you need to create a unique number to act as a primary key .

SQL> select object_name,object_type,owner from dba_objects where object_name='SOA_B2B_SEQUENCE';

OBJECT_NAME                    OBJECT_TYPE         OWNER

------------------------------ ------------------- ------------------------------

SOA_B2B_SEQUENCE               SEQUENCE            SOACONFIG

SQL> conn SOACONFIG/****;

Connected.

SQL> select dbms_metadata.get_ddl('SEQUENCE', 'SOA_B2B_SEQUENCE') from dual;

DBMS_METADATA.GET_DDL('SEQUENCE','SOA_B2B_SEQUENCE')

--------------------------------------------------------------------------------

   CREATE SEQUENCE  "SOACONFIG"."SOA_B2B_SEQUENCE"  MINVALUE 1 MAXVALUE 99999999

9999999999999999999 INCREMENT BY 1 START WITH 1951246 CACHE 20 NOORDER  NOCYCLE

=============================================

How to increment/reset sequence.

 Either we can drop and recreate . But it can be incremented with negative number .

 Increment two time ,update with increment 1

SQL> alter sequence SOA_B2B_SEQUENCE increment by -1902490;

Sequence altered.

SQL> select SOA_B2B_SEQUENCE.nextval from dual;

   NEXTVAL

----------

    999998

SQL> alter sequence SOA_B2B_SEQUENCE increment by 1;

Sequence altered.

SQL> select SOA_B2B_SEQUENCE.nextval from dual;

   NEXTVAL

----------

    999999

SQL> select SOA_B2B_SEQUENCE.nextval from dual;

   NEXTVAL

----------

   1000000

SQL> select SOA_B2B_SEQUENCE.nextval from dual;

   NEXTVAL

----------

   1000001