Sequence Definitions (dseq.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

This script provides a comprehensive view of database sequences and their configuration parameters. It displays sequence properties including value ranges, increment settings, caching behavior, and current usage levels, helping DBAs manage sequence objects and prevent exhaustion issues.

Script

rem  dseq.sql
rem
rem  linesize = 80
rem
ttitle 'Sequence Definitions'
rem
col sequence_owner format a12 heading 'OWNER'
col sequence_name format a25 heading 'SEQUENCE NAME'
col min_value format 9999 heading 'MIN|VALUE'
col max_value format 999999999 heading 'MAX|VALUE'
col increment_by format 999 heading 'INC'
col cycle_flag format a1 heading 'C'
col order_flag format a1 heading 'O'
col cache_size format 9999 heading 'CACHE|SIZE'
col last_number format 99999999 heading 'LAST|NUMBER'
rem
break on sequence_owner
rem
select sequence_owner, sequence_name, min_value, max_value,
       increment_by, cycle_flag, order_flag, cache_size, last_number
  from sys.dba_sequences
 where sequence_owner like upper('&owner')
   and sequence_name like upper('&sequence')
 order by sequence_owner, sequence_name;

Usage

-- Run the script in SQL*Plus or SQLcl
@dseq.sql

-- When prompted, enter:
-- owner: Schema owner pattern (% for all)
-- sequence: Sequence name pattern (% for all)

-- Examples
Enter value for owner: %
Enter value for sequence: %

-- Or filter specific sequences
Enter value for owner: SALES
Enter value for sequence: %ORDER%

Parameters

• &owner: Schema owner pattern (use % for wildcard)
• &sequence: Sequence name pattern (use % for wildcard)

Required Privileges

• SELECT on SYS.DBA_SEQUENCES
• Typically requires DBA role or SELECT_CATALOG_ROLE

Sample Output

Sequence Definitions

OWNER        SEQUENCE NAME             MIN   MAX       INC C O CACHE LAST
                                       VALUE VALUE           SIZE NUMBER
------------ ------------------------- ----- --------- --- - - ----- --------
HR           EMPLOYEE_ID_SEQ             100 999999999   1 N N    20   123456
             DEPARTMENT_ID_SEQ             1 999999999   1 N N    20      567

SALES        CUSTOMER_ID_SEQ               1 999999999   1 N N    50  2345678
             ORDER_ID_SEQ                  1 999999999   1 N N   100  4567890
             ORDER_LINE_SEQ                1 999999999   1 N N    25   789012

WAREHOUSE    PRODUCT_ID_SEQ                1 999999999   1 N N    50  1234567
             LOCATION_ID_SEQ               1 999999999  10 N Y   100     5670

Key Output Columns

• OWNER: Schema that owns the sequence
• SEQUENCE NAME: Name of the sequence object
• MIN VALUE: Minimum value the sequence can generate
• MAX VALUE: Maximum value the sequence can reach
• INC: Increment value for each NEXTVAL call
• C: Cycle flag (Y = cycles after reaching max, N = errors)
• O: Order flag (Y = guaranteed order in RAC, N = no guarantee)
• CACHE SIZE: Number of values cached in memory
• LAST NUMBER: Current highest value generated

Understanding Sequence Configuration

Value Range Management

• MIN/MAX VALUE: Defines sequence boundaries
• LAST NUMBER: Shows current usage level
• Remaining values: MAX_VALUE - LAST_NUMBER

Performance Settings

• CACHE SIZE: Improves performance by pre-allocating values
• ORDER flag: Impacts RAC performance (Y = slower, N = faster)
• INCREMENT BY: Step size for each nextval

Behavior Options

• CYCLE flag: Determines behavior when reaching MAX_VALUE
• Y: Restarts from MIN_VALUE (useful for certain applications)
• N: Throws error when exhausted (typical for primary keys)

Sequence Analysis

Usage Monitoring

1. High usage sequences: Large LAST_NUMBER values
2. Approaching limits: LAST_NUMBER near MAX_VALUE
3. Growth patterns: Track LAST_NUMBER changes over time

Performance Optimization

1. Cache sizing: Balance performance vs gap risk
2. Order requirements: Only use ORDER when necessary in RAC
3. Increment optimization: Consider larger increments for batch operations

Capacity Planning

1. Exhaustion prevention: Monitor sequences approaching MAX_VALUE
2. Growth estimation: Calculate time to exhaustion based on usage
3. Sequence migration: Plan for larger value ranges

Common Use Cases

Sequence Inventory

-- List all sequences
@dseq.sql
-- Enter % for both parameters
-- Review all sequence configurations

Usage Analysis

-- Find high-usage sequences
@dseq.sql
-- Enter % for both parameters
-- Look for sequences with high LAST_NUMBER values

Exhaustion Prevention

-- Check sequences approaching limits
@dseq.sql
-- Calculate remaining capacity
-- Plan sequence modifications

Performance Review

-- Analyze cache and order settings
@dseq.sql
-- Review CACHE_SIZE and ORDER_FLAG
-- Optimize for performance

Sequence Management

Creating Sequences

-- Standard sequence for primary keys
CREATE SEQUENCE sales.order_id_seq
  START WITH 1
  INCREMENT BY 1
  MAXVALUE 999999999999999999999999999
  CACHE 100
  NOORDER
  NOCYCLE;

Modifying Sequences

-- Increase cache size for performance
ALTER SEQUENCE sales.order_id_seq CACHE 500;

-- Increase maximum value
ALTER SEQUENCE sales.order_id_seq MAXVALUE 99999999999999999;

-- Reset sequence (requires special handling)
-- Cannot directly reset - must drop and recreate

Monitoring Usage

-- Track sequence usage over time
SELECT sequence_name, last_number, SYSDATE
FROM dba_sequences
WHERE sequence_owner = 'SALES';

Troubleshooting Sequence Issues

Sequence Exhaustion

1. Immediate fix:

   -- Increase maximum value
   ALTER SEQUENCE schema.sequence_name
   MAXVALUE 999999999999999999999999999;

2. Enable cycling (if appropriate):

   ALTER SEQUENCE schema.sequence_name CYCLE;

3. Reset sequence (requires drop/recreate):

   -- Get current max value from table
   SELECT MAX(id_column) FROM table_name;
   
   -- Drop and recreate sequence
   DROP SEQUENCE schema.sequence_name;
   CREATE SEQUENCE schema.sequence_name START WITH new_value;

Performance Issues

1. Small cache causing contention:

   -- Increase cache size
   ALTER SEQUENCE schema.sequence_name CACHE 1000;

2. RAC ordering overhead:

   -- Remove ordering if not required
   ALTER SEQUENCE schema.sequence_name NOORDER;

Gap Analysis

1. Check for sequence gaps:

   -- Compare sequence value to table max
   SELECT s.last_number, MAX(t.id_column) max_table_value,
          s.last_number - MAX(t.id_column) gap
   FROM dba_sequences s, table_name t
   WHERE s.sequence_name = 'SEQUENCE_NAME';

Advanced Analysis

Sequence Utilization Report

-- Calculate utilization percentage
SELECT sequence_owner, sequence_name,
       last_number, max_value,
       ROUND((last_number/max_value)*100, 2) utilization_pct,
       max_value - last_number remaining_values
FROM dba_sequences
WHERE max_value != 999999999999999999999999999
ORDER BY utilization_pct DESC;

Performance Impact Assessment

-- Identify sequences with potential performance issues
SELECT sequence_owner, sequence_name,
       cache_size, order_flag,
       CASE
         WHEN cache_size < 20 THEN 'Small Cache'
         WHEN order_flag = 'Y' THEN 'RAC Ordering Overhead'
         ELSE 'OK'
       END performance_concern
FROM dba_sequences
WHERE cache_size < 20 OR order_flag = 'Y';

Dependency Analysis

-- Find tables using sequences (approximate)
SELECT table_name, column_name, data_default
FROM dba_tab_columns
WHERE data_default LIKE '%.nextval'
OR data_default LIKE '%NEXTVAL';

Best Practices

Sequence Design

1. Use large maximum values to prevent exhaustion
2. Cache appropriately: 20-100 for OLTP, higher for batch
3. Avoid ORDER in RAC unless specifically required
4. Don’t cycle primary key sequences

Monitoring Strategy

1. Regular capacity checks: Monitor sequences approaching limits
2. Performance monitoring: Track sequences with contention
3. Growth analysis: Estimate future requirements
4. Documentation: Maintain sequence usage documentation

Related Scripts

• Large Usage Sequences (dseqbig.sql) - Focus on high-usage sequences
• Object Count Analysis (../administration/objcnt.md) - Object inventory including sequences
• Schema Objects (dobject.sql) - Complete object analysis
• Table Analysis (dtable.sql) - Table definitions that may use sequences