Oracle Sequences

more..

 

Full Create Sequence Syntax

CREATE SEQUENCE <sequence_name>
INCREMENT BY <integer>
START WITH <integer>
MAXVALUE <integer> / NOMAXVALUE
MINVALUE <integer> / NOMINVALUE
CYCLE / NOCYCLE
CACHE <#> / NOCACHE
ORDER / NOORDER;

Create Sequence Simplest Form

CREATE SEQUENCE <sequence_name>;

Create Sequence START WITH Form 

CREATE SEQUENCE <sequence_name> START WITH <integer>;

Create Sequence INCREMENT BY Form

 CREATE SEQUENCE <sequence_name> INCREMENT BY <positive integer>;

Create Sequence Reverse DECREMENT BY Form

CREATE SEQUENCE <sequence_name>
MAX VALUE <integer value>
INCREMENT BY <negative integer>;

Create Sequence CACHE Form ( default cache is 20 )

CREATE SEQUENCE <sequence_name> CACHE <integer>;

Create Sequence CYCLE Form

CREATE SEQUENCE <sequence_name> START WITH <integer>
MAXVALUE <integer> CYCLE;

Insert Syntax

 INSERT INTO <table_name>
(<column_name>)
VALUES
(<sequence_name>.CURRVAL);

Alter Sequence

Change Increment ALTER SEQUENCE <sequence_name> INCREMENT BY <integer>;
ALTER SEQUENCE seq_inc_by_ten INCREMENT BY 20;
Change Max Value ALTER SEQUENCE <sequence_name> MAX VALUE <integer>
ALTER SEQUENCE seq_maxval MAXVALUE 10;
Change Cycle ALTER SEQUENCE <sequence_name> <CYCLE | NOCYCLE>
ALTER SEQUENCE seq_cycle NOCYCLE;
Change Cache ALTER SEQUENCE <sequence_name> CACHE <integer> | NOCACHE
ALTER SEQUENCE seq_cache NOCACHE;
Change Order ALTER SEQUENCE <sequence_name> <ORDER | NOORDER>
ALTER SEQUENCE seq_order NOORDER;

Drop Sequence

Drop Sequence DROP SEQUENCE <sequence_name>;
DROP SEQUENCE seq_cache;

Last Number Selected From Sequence

SELECT sequence_name, last_number
FROM user_sequences;

Next Number From Sequence

SELECT sequence_name, (last_number + increment_by) NEXT_VALUE
FROM user_sequences;

Sequence Resets 

  By finding out the current value of the sequence and altering the increment by to be negative that number and selecting the sequence once -- the sequence can be reset to 0.
If any session attempts to use the sequence while this is happening an ORA-08004 error will be generated.