SEQUENCE IN ORACLE

SEQUENCE :

-- Sequence automatically generates UNIQUE numbers.
-- It is shema object and sharable object.
-- It is mainly used for generating primary key column values.
-- NEXTVAL, CURRVAL are sequence attributes.

Syntax

CREATE SEQUENCE sequence_name
  MINVALUE value
  MAXVALUE value
  START WITH value
  INCREMENT BY value
  CACHE value;
  
CREATE SEQUENCE supplier_seq
  MINVALUE 1
  MAXVALUE 25
  START WITH 1
  INCREMENT BY 1
  CACHE 20;
  
--Here Minvalue 1 , maxvalue is 25 if you not mentioning the maxvalue its default take upto 999999999999999999999999999, started values as 1 and increment by 1 for every run.

--The first sequence number that it would use is 1 and each subsequent number would increment by 1 (ie: 2,3,4,...}. It will cache up to 20 values for performance.
  
CACHE is used to increase the performance of the sequence.
  
SQL> CREATE table customer(supplier_id number, supplier_name varchar2(10));

Table created.

SQL> CREATE SEQUENCE customer_1
  2  MINVALUE 1
  3  START WITH 1
  4  INCREMENT BY 1
  5  CACHE 20;

Sequence created.

NEXTVAL

SQL> INSERT into customer values(customer_1.nextval,'parthi');
SQL> INSERT into customer values(customer_1.nextval,'karthi');
SQL> INSERT into customer values(customer_1.nextval,'arthi');

SQL> select * from customer;

SUPPLIER_ID SUPPLIER_N
----------- ----------
          1 parthi
          2 karthi
          3 arthi

SQL> ALTER SEQUENCE customer_1
  2  maxvalue 6;

Sequence altered.

SQL> INSERT into customer values(customer_1.nextval,'vimal');
SQL> INSERT into customer values(customer_1.nextval,'kamal');
SQL> INSERT into customer values(customer_1.nextval,'siva');

SQL> INSERT into customer values(customer_1.nextval,'vicky');
INSERT into customer values(customer_1.nextval,'vicky')
                            *
ERROR at line 1:
ORA-08004: sequence CUSTOMER_1.NEXTVAL exceeds MAXVALUE and cannot be
instantiated

CURRVAL

SQL> SELECT customer_1.CURRVAL FROM customer;

   CURRVAL
----------
         6