SEQUENCE

Databases

Oracle Database

Schema

Check

Check

SELECT *

FROM dba_sequences

WHERE sequence_name = 'MYSEQUENCE';

Usage

SELECT mysequence.nextval FROM dual;

SELECT mysequence.currval FROM dual;

Note that selecting nextval will increment the sequence. But you can't select currval before you have selected nextval (you get an ORA-08002). If you need to know the current value and you don't want to increment it first, then use the LAST_NUMBER field from DBA_SEQUENCES (using the query from the Check section above).

CREATE

CREATE SEQUENCE mysequence

MAXVALUE 9999999999999999999999999999;

MAXVALUE must have 28 or fewer digits

CREATE SEQUENCE mysequence

NOMAXVALUE;

This is equivalent to using the 28 nines as shown in the previous example

CREATE SEQUENCE mysequence

MINVALUE 1

NOMAXVALUE

INCREMENT BY 1

CACHE 100

NOCYLCE;

ALTER

Restart

ALTER SEQUENCE mysequence RESTART START WITH 999;

Bibliography

https://stackoverflow.com/questions/9595876/what-is-the-maximum-value-of-maxvalue-in-sequence-in-oraclehttps://www.yawintutor.com/ora-08004-sequence-mysequence-nextval-exceeds-maxvalue-and-cannot-be-instantiated/https://stackoverflow.com/questions/26990845/why-do-i-get-error-ora-08002-when-trying-to-get-the-currval-of-a-sequence
21https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/CREATE-SEQUENCE.html
19https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/ALL_SEQUENCES.html
10.2https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_2011.htm (ALTER SEQUENCE)

Page updated

Google Sites

Report abuse