DBA_SEQUENCES in Oracle: Concept and Example

DBA_SEQUENCES in Oracle: Concept and Example

Concept:

In Oracle, a sequence is a database object that is used to generate a sequence of unique numbers. These numbers can be used to create unique identifiers for rows in a table, typically for a primary key. A sequence is particularly useful when you need to generate unique values across multiple sessions and transactions.

The DBA_SEQUENCES view provides information about all sequences in the database that are accessible to the user. It displays details like the sequence name, minimum and maximum values, increment value, current value, and more.

Key Columns in DBA_SEQUENCES:

• SEQUENCE_NAME: Name of the sequence.
• MIN_VALUE: Minimum value that the sequence can generate.
• MAX_VALUE: Maximum value that the sequence can generate.
• INCREMENT_BY: The interval between numbers in the sequence.
• CYCLE_FLAG: Indicates whether the sequence should restart from the MIN_VALUE after reaching the MAX_VALUE.
• ORDER_FLAG: Indicates whether sequence numbers are generated in order of request.
• CACHE_SIZE: Number of sequence numbers that will be preallocated and stored in memory for faster access.
• LAST_NUMBER: The last number generated by the sequence (or the last number stored in the cache).

Creating a Sequence:

To create a sequence, you use the CREATE SEQUENCE statement.

CREATE SEQUENCE emp_seq
  START WITH 1          -- Starting value of the sequence
  INCREMENT BY 1        -- Increment by 1 for each subsequent value
  MINVALUE 1            -- Minimum value
  MAXVALUE 9999         -- Maximum value
  NOCYCLE               -- Do not cycle back to the start value when max value is reached
  CACHE 20;             -- Cache 20 sequence numbers for faster access

Using a Sequence:

You can use the sequence to generate values in an INSERT statement, like so:

INSERT INTO employees (employee_id, first_name, last_name)
VALUES (emp_seq.NEXTVAL, 'John', 'Doe');

Here, emp_seq.NEXTVAL generates the next value in the sequence emp_seq, and that value is inserted into the employee_id column.

Viewing Sequence Information:

To view information about a sequence, you can query the DBA_SEQUENCES view.

SELECT sequence_name, min_value, max_value, increment_by, last_number
FROM dba_sequences
WHERE sequence_name = 'EMP_SEQ';

Example:

Let's say you have a table called EMPLOYEES and you want to ensure that each employee has a unique EMPLOYEE_ID. You can create a sequence to generate these IDs.

1. Create the EMPLOYEES Table:

   CREATE TABLE employees (
       employee_id NUMBER PRIMARY KEY,
       first_name VARCHAR2(50),
       last_name VARCHAR2(50)
   );
   

2. Create a Sequence:

   
   CREATE SEQUENCE emp_seq
     START WITH 100
     INCREMENT BY 10
     NOCACHE;
   

   This sequence starts at 100 and increments by 10 each time a new value is generated.

3. Insert Data Using the Sequence:

   
   INSERT INTO employees (employee_id, first_name, last_name)
   VALUES (emp_seq.NEXTVAL, 'Alice', 'Smith');
   
   INSERT INTO employees (employee_id, first_name, last_name)
   VALUES (emp_seq.NEXTVAL, 'Bob', 'Johnson');
   

   The first insert will give employee_id the value 100, and the second insert will give it the value 110.

4. Check the Data:

   SELECT * FROM employees;

   The output will show the EMPLOYEE_ID values as 100 and 110, respectively.

Conclusion:

Sequences are essential for generating unique numbers automatically, especially when dealing with primary keys or any other unique identifiers in a database. By using DBA_SEQUENCES, you can monitor and manage these sequences effectively in Oracle.