Sequences

Create

CREATE SEQUENCE [schema.]<SequenceName>
   MINVALUE     integer
   [MAXVALUE    integer]
   INCREMENT BY integer
   START WITH   integer
   [CACHE #|NOCACHE] 
   [ORDER|NOORDER] 
   [CYCLE|NOCYCLE];

CREATE SEQUENCE hr.seq_account_id
MINVALUE 1
INCREMENT BY 1
START WITH 1001;

Display

-- All Sequences for a Schema

SELECT sequence_name,last_number, max_value 
FROM dba_sequences
WHERE sequence_owner='&SCHEMA'
ORDER BY sequence_owner,sequence_name;

-- A Particular Sequence

SELECT sequence_owner,last_number
FROM dba_sequences
WHERE sequence_name = 'MY_SEQ_NAME';

Drop

DROP SEQUENCE [schema.]<SequenceName>;

DROP SEQUENCE hr.seq_account_id;

Usage

SELECT hr.seq_account_id.NEXTVAL FROM dual;

INSERT INTO emp (emp_id, name)
VALUES (hr.seq_account_id.NEXTVAL, 'John Smith');

<- Schema