Sequences (Autonumber) Drop, Create, Alter commands.

In Oracle, you can create an autonumber field by using sequences. 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.

Oracle SQL Syntax :

SELECT last_number
  FROM all_sequences
 WHERE sequence_owner = ''
   AND sequence_name = '';

You can get a variety of sequence metadata from user_sequencesall_sequences and dba_sequences.Show tables’ max sequence value:

SELECT MAX(id) FROM CRM.CPM_PLANLI_CALISMA; --- The name of the sequence that you wish to create. 

 MAX(ID)
----------
 16503  --- table's max value.
1 row selected.

old sequence scripts:

DROP SEQUENCE CRM.CPM_PLANLI_CALISMA_SEQ;

CREATE SEQUENCE CRM.CPM_PLANLI_CALISMA_SEQ
  START WITH 1300 --- old sequence's current values.
  MAXVALUE 9999999999999999999999999999
  MINVALUE 1
  NOCYCLE
  CACHE 20
  NOORDER;

now, we are creating a new sequence as a value:

before: we must delete old sequence :

DROP SEQUENCE CRM.CPM_PLANLI_CALISMA_SEQ;
sequence deleted.

after : we are creating a new sequence :

CREATE SEQUENCE CRM.CPM_PLANLI_CALISMA_SEQ
START WITH 16503 --- new secuence's value.
MAXVALUE 999999999999999999999999999
MINVALUE 1
NOCYCLE
NOCACHE
NOORDER;
sequence created.

let me check :

SELECT last_number
FROM all_sequences
WHERE sequence_owner = 'CRM'
AND sequence_name = 'CPM_PLANLI_CALISMA_SEQ';

LAST_NUMBER
-----------
16503 
1 row selected.

Have a good day

Hits: 4

Leave a Reply

Your email address will not be published. Required fields are marked *