Oracle Tutorial

  • Home
  • Start Here
  • Basics
  • Advanced
    • Oracle View
    • Oracle Index
    • Oracle Synonym
    • Oracle Sequence
    • Oracle Administration
  • PL/SQL
  • Functions
    • Aggregate Functions
    • Analytic Functions
    • Comparison Functions
    • Date Functions
    • String Functions
  • API
    • Python Oracle
Home / Oracle Sequence

Oracle Sequence

What is a sequence

A sequence is a list of integers in which their orders are important. For example, the (1,2,3,4,5) and (5,4,3,2,1) are totally different sequences even though they have the same members.

Creating a sequence

The CREATE SEQUENCE statement allows you to create a new sequence object in your own schema.

For example, this statement uses the CREATE SEQUENCE statement to create a new sequence object named item_seq:

CREATE SEQUENCE item_seq;

You use the sequence object to generate a sequence of unique integers, mostly for surrogate key columns.

Note that Oracle 12c automatically generates a sequence object associated with the identity column of the table.

Using a sequence

To access the next available value for a sequence, you use the NEXTVAL pseudo-column:

SELECT item_seq.NEXTVAL FROM dual;
   NEXTVAL
----------
         1

Once, you acquire the sequence number through the NEXTVAL pseudo-column, you can access it repeatedly using the CURRVAL pseudo-column:

SELECT item_seq.CURRVAL FROM dual;
CURRVAL ---------- 1

The following statement uses the item_seq sequence repeatedly in an SQL statement:

SELECT item_seq.NEXTVAL FROM dual CONNECT BY level <= 5;
   NEXTVAL
----------
         2
         3
         4
         5
         6

This example uses the item_seq sequence in the INSERT statements to populate values for the item_id column of the items table:

CREATE TABLE items( item_id NUMBER ); INSERT INTO items(item_id) VALUES(item_seq.NEXTVAL); INSERT INTO items(item_id) VALUES(item_seq.NEXTVAL); COMMIT; SELECT item_id FROM items;

Here is the output:

ITEM_ID ---------- 7 8

From Oracle 11g onward, you can use sequences in PL/SQL. Behind the scenes, Oracle still uses a query from the dual table, but it makes the code cleaner:

DECLARE v_seq NUMBER; BEGIN v_seq := item_seq.NEXTVAL; DBMS_OUTPUT.put_line('v_seq=' || v_seq); END;
v_seq=9

Modifying a sequence

To modify the attributes and behavior of an existing sequence object, you use the ALTER SEQUENCE statement.

The following example uses the ALTER SEQUENCE to set the maximum value for the item_seq to 100:

ALTER SEQUENCE item_seq MAXVALUE 100;

Removing a sequence

To remove an existing sequence from the database, you use the DROP SEQUENCE statement. The following example uses the DROP SEQUENCE statement to delete the item_seq sequence:

DROP SEQUENCE item_seq;

Oracle sequence privileges

Oracle provides the CREATE SEQUENCE system privilege to allow you to create, alter, and drop sequences.

This statement grants a user the CREATE SEQUENCE privilege:

GRANT CREATE SEQUENCE TO user_name;

In addition, Oracle provides the following privileges that allow you to manipulate sequences in all schemas, including the built-in ones:

  • CREATE ANY SEQUENCE
  • ALTER ANY SEQUENCE
  • DROP ANY SEQUENCE
  • SELECT ANY SEQUENCE

Therefore, you should consider seriously before executing the following command:

GRANT CREATE ANY SEQUENCE, ALTER ANY SEQUENCE, DROP ANY SEQUENCE, SELECT ANY SEQUENCE TO user_name;

If you are the owner of the sequence, you will have the full privileges on the sequence. To grant another user access to a sequence, you can grant the SELECT object privilege to that user as shown in the following command:

GRANT SELECT ON user_name.sequence_name TO another_user;

More on sequences

  • CREATE SEQUENCE – create a new sequence object in the database.
  •  ALTER SEQUENCE – modify the attributes and behaviors of an existing sequence.
  •  DROP SEQUENCE – drop an existing sequence.

In this tutorial, you have learned about Oracle sequence including creating, using, modifying, and removing a sequence.

  • Was this tutorial helpful?
  • YesNo
Previous Oracle Synonym
Next PL/SQL Tutorial

Getting Started

  • What Is Oracle Database
  • Install Oracle Database Server
  • Download Oracle Sample Database
  • Create Oracle Sample Database
  • Connect To Oracle Database Server

Oracle Data Manipulation

  • SELECT
  • Oracle DUAL Table
  • ORDER BY
  • SELECT DISTINCT
  • WHERE
  • Table & Column Aliases
  • AND
  • OR
  • FETCH
  • BETWEEN
  • IN
  • LIKE
  • IS NULL
  • Joins
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL OUTER JOIN
  • CROSS JOIN
  • Self Join
  • GROUP BY
  • HAVING
  • UNION
  • INTERSECT
  • MINUS
  • GROUPING SETS
  • CUBE
  • ROLLUP
  • PIVOT
  • UNPIVOT
  • INSERT
  • INSERT INTO SELECT
  • INSERT ALL
  • UPDATE
  • DELETE
  • MERGE
  • Subquery
  • Correlated Subquery
  • EXISTS
  • NOT EXISTS
  • ANY
  • ALL

Oracle Data Types

  • Oracle Data Types
  • NUMBER
  • FLOAT
  • BINARY_FLOAT
  • CHAR
  • NCHAR
  • VARCHAR2
  • NVARCHAR2
  • DATE
  • INTERVAL
  • TIMESTAMP
  • TIMESTAMP WITH TIME ZONE

Oracle Data Definition

  • CREATE TABLE
  • Identity Column
  • ALTER TABLE
  • ALTER TABLE ADD Column
  • ALTER TABLE MODIFY Column
  • Drop Columns
  • DROP TABLE
  • TRUNCATE TABLE
  • RENAME Table
  • Oracle Virtual Column

Oracle Constraints

  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE
  • CHECK
  • NOT NULL

Oracle Views

  • CREATE VIEW
  • DROP VIEW
  • Updatable Views
  • Inline Views
  • WITH CHECK OPTION

About Oracle Tutorial

OracleTututorial.com website provides Developers and Database Administrators with the updated Oracle tutorials, scripts, and tips.

Search

Recent Tutorials

  • Oracle Implicit Statement Results
  • Calling PL/SQL Stored Functions in Python
  • Calling PL/SQL Procedures in Python
  • Managing Transaction in Python
  • Deleting Data From Oracle Database in Python

Site Links

  • Oracle Books
  • About
  • Contact
  • Privacy Policy
  • Terms of Use

Copyright © 2021 Oracle Tutorial. All Rights Reserved.