Using a Sequence

Using a Sequence
ex)

INSERT INTO department
  (department_id, department_name, location_id)
VALUES
  (dept_deptid_seq.NEXTVAL, 'Support', 2500 )

Tip) View current value

SELECT dept_deptid_seq.CURRVAL FROM dual;

Caching Sequence Values

- Using Cache function : faster access to those values.
- Gaps in sequence values can occur when:
  A rollback occurs
  The system crashes
  A sequence is used in another table

Modifying a Sequence

ALTER SEQUENCE dept_deptid_seq
                            INCREMENT BY 20
                           MAXVALUE 999999
                           NOCACHE
                           NOCYCLE;

If you reach the MAXVALUE limit for your sequence, no additional values from the sequence are allocated and you will received an error.

Syntax)

ALTER SEQUENCE sequence
[INCREMENT BY n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}];

1. Must have the ALTER privilege
2. Only future sequence numbers are affected
3. To start at a different, the sequence must be dropped and re-created
4. validation
5. To remove a sequence, use the DROP startement

DROP SEQUENCE dept_deptid_seq;